|
Home > Archive > PHP with PostgreSQL > November 2006 > Re: PostgreSQL locking from PHP scripts
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: PostgreSQL locking from PHP scripts
|
|
| Alan Hodgson 2006-11-07, 7:19 pm |
| | |
| Alan Hodgson 2006-11-07, 7:19 pm |
| | |
| Gavin M. Roy 2006-11-07, 7:19 pm |
| Well you can use persistent connections. Instead of pg_connect use
pg_pconnect. I don't know if it will keep the lock or not, I've not
tested, but it seems like you'd need a pretty elaborate connection
management setup to make sure you're locking and unlocking the
records for the right client.
On Nov 6, 2006, at 11:05 AM, Alan Hodgson wrote:
> On Thursday 26 October 2006 11:11, Amal burman
> <amalburman@yahoo.com> wrote:
>
> You can't do this kind of thing in PHP. PHP resets the database
> connection
> between scripts. The script is finished as soon as the page is
> delivered
> to the client.
>
> Also, web connections are stateless and there is no way to even
> know what
> web server process or database connection you'll be getting on
> subsequent
> page views.
>
> --
> "It is a besetting vice of democracies to substitute public opinion
> for
> law." - James Fenimore Cooper
>
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
| |
| Herbie McDuck 2006-11-07, 7:19 pm |
| Alan Hodgson wrote:
> On Thursday 26 October 2006 11:11, Amal burman <amalburman@yahoo.com> wrote:
>
>
> You can't do this kind of thing in PHP. PHP resets the database connection
> between scripts. The script is finished as soon as the page is delivered
> to the client.
>
> Also, web connections are stateless and there is no way to even know what
> web server process or database connection you'll be getting on subsequent
> page views.
>
>
So is PHP and 'ANY' database a useless adventure when it goes to
developing a robust business and accounting package?
--Hal.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
|
| Herbie McDuck wrote:
> Alan Hodgson wrote:
> So is PHP and 'ANY' database a useless adventure when it goes to
> developing a robust business and accounting package?
No, it applies to anything you do in a web browser.
The HTTP protocol is "stateless" which means all resources, connections
etc are killed at the end of the script. Resources of any type are not
kept alive at the end of a script.
It's not a php thing or <insert random language here>, it's a HTTP
protocol thing.
--
Postgresql & php tutorials
http://www.designmagick.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Charley Tiggs 2006-11-07, 7:19 pm |
| Herbie McDuck wrote:
> So is PHP and 'ANY' database a useless adventure when it goes to
> developing a robust business and accounting package?
Not at all. It just means that you'll have to create your own "state"
logic using sessions. For instance, create a column within the
necessary table that you can check to see whether or not some other
process/user is using the record in question and then build the
necessary logic around that to enable/disable access to the records.
This is something that isn't specific to PHP. You'll encounter the same
issues with any other middleware language that uses a web browser as
it's primary means of interaction. HTTP just doesn't keep track of the
state of your application. It's up to you as the developer to handle that.
Charley
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
| |
| Robert Treat 2006-11-07, 7:19 pm |
| On Monday 06 November 2006 20:24, Chris wrote:
> Herbie McDuck wrote:
>
> No, it applies to anything you do in a web browser.
>
> The HTTP protocol is "stateless" which means all resources, connections
> etc are killed at the end of the script. Resources of any type are not
> kept alive at the end of a script.
>
> It's not a php thing or <insert random language here>, it's a HTTP
> protocol thing.
In theory you can use prepared transactions to get around this, but I've never
seen anyone fully implement it in PHP.
--
Robert Treat
Build A Brighter LAMP :: Linux apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Herbie McDuck 2006-11-07, 7:19 pm |
| Charley Tiggs wrote:
> Herbie McDuck wrote:
>
> Not at all. It just means that you'll have to create your own "state"
> logic using sessions. For instance, create a column within the
> necessary table that you can check to see whether or not some other
> process/user is using the record in question and then build the
> necessary logic around that to enable/disable access to the records.
>
> This is something that isn't specific to PHP. You'll encounter the
> same issues with any other middleware language that uses a web browser
> as it's primary means of interaction. HTTP just doesn't keep track of
> the state of your application. It's up to you as the developer to
> handle that.
>
> Charley
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>
Hmmm..Yes I can see two columns in a record. The 'WHO' and some pending
transaction ID that the calling session that locked the record knows
about so that the update session can find the relevant and specific
record to transact with.
As mentioned, I've done something similar in another language. When you
have ten people trying to sell low sulfur diesel fuel at the same moment
in time, record contention can become dicey at best.
--Hal.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Rick Morris 2006-11-07, 7:19 pm |
| Robert Treat wrote:
> On Monday 06 November 2006 20:24, Chris wrote:
>
>
> In theory you can use prepared transactions to get around this, but I've never
> seen anyone fully implement it in PHP.
>
>
How exactly would that work? With PDO Prepare/Execute? Would you prepare
a transaction, and then store the PDOStatement object as a session var?
Or do you mean this is something that would have to be implemented in
PHP internals?
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Rick Morris 2006-11-07, 7:19 pm |
| Chris wrote:
> Herbie McDuck wrote:
>
> No, it applies to anything you do in a web browser.
>
> The HTTP protocol is "stateless" which means all resources,
> connections etc are killed at the end of the script. Resources of any
> type are not kept alive at the end of a script.
>
> It's not a php thing or <insert random language here>, it's a HTTP
> protocol thing.
>
There is another way to approach this with PHP and other random
languages, as long as scripts can be executed outside of the context of
an HTTP server module (such as PHP in CLI mode): have a the web script
interact with a standalone commandline script or daemon that lives
across requests. Thus your HTTP-requested PHP script could communicate
via some IPC method to a PHP-CLI script which holds the database
connections, binds them to a session ID and actually handles the
mechanics of transactions and queries (a simple connection pooling
method, if you will) and thus transactions can be maintained across
multiple requests.
Now, saying it and doing it are two different things, I understand; not
exactly trivial to implement. But still, if it is important enough to
your application, it can be done. And, I have found PHP to be quite
stable running in daemon mode. At least, amply stable enough to handle
transactions that span minutes or hours. (I have had PHP daemon scripts
run for weeks without problem)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Robert Treat 2006-11-08, 12:27 am |
| On Tuesday 07 November 2006 02:54, Rick Morris wrote:
> Robert Treat wrote:
>
> How exactly would that work? With PDO Prepare/Execute? Would you prepare
> a transaction, and then store the PDOStatement object as a session var?
>
Your thinking of prepared statements, which are something different entirely.
I'm talking about prepared transactions, which are typically used for 2 phase
commit implementations.
http://www.postgresql.org/docs/8.1/...ransaction.html
> Or do you mean this is something that would have to be implemented in
> PHP internals?
Actually it canall be done at the SQL level, although if you were going to do
it seriously I'd suspect that you'd want to add some php internal functions
to do it, though aiui there aren't any knobs you'd get from libpq to do this,
so not sure what that would like like on the php end.
In any case upon a bit more reflection prepared transactions really wouldn't
allow you to do what the OP wanted, so it's all kind of moot.
--
Robert Treat
Build A Brighter LAMP :: Linux apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
|
|
|
|