Обсуждение: PostgreSQL and PHP persistent connections
Thies, we talked at LinuxWorld about improving the reliability of persistent PostgreSQL connections in PHP. I believe the problem is that persistent connections sometime do not pass clean sessions because of open transactions and SET changes to the session. We discussed using 'ROLLBACK' before passing a connection to a new user, but the problem was that ROLLBACK with no open transaction causes a server log error message. We discussed adding 'ROLLBACK SILENT' to fix this, but I believe a better, more portable solution is a simple "BEGIN WORK;ROLLBACK". This will do nothing if there is no open transaction, and will ROLLBACK any open transaction. I propose this be sent by PHP as the first query when passing persistent connections. As far as SET changes, does anyone on the PostgreSQL interfaces list have a suggestion on how to RESET all session parameters? Seems we may need to add this feature in to the backend. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We discussed using 'ROLLBACK' before passing a connection to a new user,
> but the problem was that ROLLBACK with no open transaction causes a
> server log error message. We discussed adding 'ROLLBACK SILENT' to fix
> this, but I believe a better, more portable solution is a simple "BEGIN
> WORK;ROLLBACK". This will do nothing if there is no open transaction,
How is that an improvement? It only changes the case in which you get
a NOTICE from not-in-transaction to in-transaction ...
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > We discussed using 'ROLLBACK' before passing a connection to a new user, > > but the problem was that ROLLBACK with no open transaction causes a > > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > > this, but I believe a better, more portable solution is a simple "BEGIN > > WORK;ROLLBACK". This will do nothing if there is no open transaction, > > How is that an improvement? It only changes the case in which you get > a NOTICE from not-in-transaction to in-transaction ... Well, the most common case is that you are not in a transaction. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
At 14:12 07/02/01 -0500, Bruce Momjian wrote: >Thies, we talked at LinuxWorld about improving the reliability of >persistent PostgreSQL connections in PHP. > >I believe the problem is that persistent connections sometime do not >pass clean sessions because of open transactions and SET changes to the >session. > >We discussed using 'ROLLBACK' before passing a connection to a new user, >but the problem was that ROLLBACK with no open transaction causes a >server log error message. We discussed adding 'ROLLBACK SILENT' to fix >this, but I believe a better, more portable solution is a simple "BEGIN >WORK;ROLLBACK". This will do nothing if there is no open transaction, >and will ROLLBACK any open transaction. I propose this be sent by PHP >as the first query when passing persistent connections. > >As far as SET changes, does anyone on the PostgreSQL interfaces list >have a suggestion on how to RESET all session parameters? Seems we may >need to add this feature in to the backend. Some Java apps utilise connection pools so they would possibly benefit. I could add the BEGIN WORK;ROLLBACK; to JDBC. Peter
At 15:50 07/02/01 -0500, Bruce Momjian wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > We discussed using 'ROLLBACK' before passing a connection to a new user, > > > but the problem was that ROLLBACK with no open transaction causes a > > > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > > > this, but I believe a better, more portable solution is a simple "BEGIN > > > WORK;ROLLBACK". This will do nothing if there is no open transaction, > > > > How is that an improvement? It only changes the case in which you get > > a NOTICE from not-in-transaction to in-transaction ... > >Well, the most common case is that you are not in a transaction. Also (JDBC again), it would ensure that no transaction was around when a connection was passed to a new client. By default it should be in autocommit mode. Peter >-- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > We discussed using 'ROLLBACK' before passing a connection to a new user, > > > but the problem was that ROLLBACK with no open transaction causes a > > > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > > > this, but I believe a better, more portable solution is a simple "BEGIN > > > WORK;ROLLBACK". This will do nothing if there is no open transaction, > > > > How is that an improvement? It only changes the case in which you get > > a NOTICE from not-in-transaction to in-transaction ... > > Well, the most common case is that you are not in a transaction. Why not tracing PQcmdStatus(result) on the persistent connection? It allways returns "BEGIN", "COMMIT" or "ROLLBACK" for whatever you issued. Well, it'll not catch the uncommon case that someone might issue multiple queries in one statements (semicolon separated). But that's IMHO bad practice anyway and the above will avoid communication, context switching andeating up of XID's until we have silent rollback in 7.2. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
> > > How is that an improvement? It only changes the case in which you get > > > a NOTICE from not-in-transaction to in-transaction ... > > > > Well, the most common case is that you are not in a transaction. > > Why not tracing PQcmdStatus(result) on the persistent > connection? It allways returns "BEGIN", "COMMIT" or > "ROLLBACK" for whatever you issued. > > Well, it'll not catch the uncommon case that someone might > issue multiple queries in one statements (semicolon > separated). But that's IMHO bad practice anyway and the above > will avoid communication, context switching and eating up of > XID's until we have silent rollback in 7.2. It is the multi-command issue that prevented me from suggesting checking the return status. If they are leaving an open transaction after disconnecting, they could be doing any crazy thing. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > We discussed using 'ROLLBACK' before passing a connection to a new user, > > but the problem was that ROLLBACK with no open transaction causes a > > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > > this, but I believe a better, more portable solution is a simple "BEGIN > > WORK;ROLLBACK". This will do nothing if there is no open transaction, > > and will ROLLBACK any open transaction. I propose this be sent by PHP > > as the first query when passing persistent connections. > > was there a conclusion made yet (that i missed somehow) on > this subject? i'd like to implement *something* in the PHP > core for this soon. One person objected that BEGIN;ROLLBACK will throw an error to the log file if there was an open transaction, but I think that is fine as this is a rare occurance, and an argument can be made that leaving an open transaction on session close in a pooled connection environment should throw some kind of error anyway. Also, this fix will work for all versions of PostgreSQL, so it can be implemented right away with no backend changes or version checks. We still need a global RESET, but I don't think that can be done until 7.2. I will add that to our project TODO list. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, Feb 07, 2001 at 02:12:26PM -0500, Bruce Momjian wrote: > Thies, we talked at LinuxWorld about improving the reliability of > persistent PostgreSQL connections in PHP. cc'ing php-dev bruce, hope you had a successful show! > > I believe the problem is that persistent connections sometime do not > pass clean sessions because of open transactions and SET changes to the > session. yep... > > We discussed using 'ROLLBACK' before passing a connection to a new user, > but the problem was that ROLLBACK with no open transaction causes a > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > this, but I believe a better, more portable solution is a simple "BEGIN > WORK;ROLLBACK". This will do nothing if there is no open transaction, > and will ROLLBACK any open transaction. I propose this be sent by PHP > as the first query when passing persistent connections. i'll have a look at that tomorrow (if my family allows;-). if "BEGIN WORK;ROLLBACK" does not stack transactions i thinkyou might have found the solution to the php-postgres problem! do you know how other script-interfaces (perl) topostgres handle the very same thing? > > As far as SET changes, does anyone on the PostgreSQL interfaces list > have a suggestion on how to RESET all session parameters? Seems we may > need to add this feature in to the backend. with the oracle driver (i wrote) there is a neat thing in the oci-libs: you have a server-handle _and_ a session handle. the session handle sits "on" the server-handle and keeps _all_ session specific data, the server handle "only"carries the pure connection to oracle. so i keep the server handle persistent and allocate/free session handleson it for each request to PHP. that way the sessions are always clean. but i also do a forces rollback on thesession handle before i free it on request-end so that in case of a script error all outstanding transactions arerolled-back. regards, tc
On Wed, Feb 07, 2001 at 02:12:26PM -0500, Bruce Momjian wrote: > Thies, we talked at LinuxWorld about improving the reliability of > persistent PostgreSQL connections in PHP. > > I believe the problem is that persistent connections sometime do not > pass clean sessions because of open transactions and SET changes to the > session. > > We discussed using 'ROLLBACK' before passing a connection to a new user, > but the problem was that ROLLBACK with no open transaction causes a > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > this, but I believe a better, more portable solution is a simple "BEGIN > WORK;ROLLBACK". This will do nothing if there is no open transaction, > and will ROLLBACK any open transaction. I propose this be sent by PHP > as the first query when passing persistent connections. was there a conclusion made yet (that i missed somehow) on this subject? i'd like to implement *something* in the PHP core for this soon. regards, tc
On Fri, Feb 09, 2001 at 10:19:51AM -0500, Bruce Momjian wrote: > > > We discussed using 'ROLLBACK' before passing a connection to a new user, > > > but the problem was that ROLLBACK with no open transaction causes a > > > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > > > this, but I believe a better, more portable solution is a simple "BEGIN > > > WORK;ROLLBACK". This will do nothing if there is no open transaction, > > > and will ROLLBACK any open transaction. I propose this be sent by PHP > > > as the first query when passing persistent connections. > > > > was there a conclusion made yet (that i missed somehow) on > > this subject? i'd like to implement *something* in the PHP > > core for this soon. > > One person objected that BEGIN;ROLLBACK will throw an error to the log > file if there was an open transaction, but I think that is fine as this > is a rare occurance, and an argument can be made that leaving an open > transaction on session close in a pooled connection environment should > throw some kind of error anyway. Also, this fix will work for all > versions of PostgreSQL, so it can be implemented right away with no > backend changes or version checks. ok, i'll start playing around with this solution soon. i'll commit it to the PHP 4 cvs as soon as i'm happy with it. thies > > We still need a global RESET, but I don't think that can be done until > 7.2. I will add that to our project TODO list.
> > We discussed using 'ROLLBACK' before passing a connection to a new user, > > but the problem was that ROLLBACK with no open transaction causes a > > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > > this, but I believe a better, more portable solution is a simple "BEGIN > > WORK;ROLLBACK". This will do nothing if there is no open transaction, > > and will ROLLBACK any open transaction. I propose this be sent by PHP > > as the first query when passing persistent connections. > > i'll have a look at that tomorrow (if my family allows;-). if > "BEGIN WORK;ROLLBACK" does not stack transactions i think you > might have found the solution to the php-postgres problem! do If we every get nested transactions, this will no longer work, but we don't have them, and will not for a while. > you know how other script-interfaces (perl) to postgres > handle the very same thing? They don't handle them, but our Java interface just added this feature. > > As far as SET changes, does anyone on the PostgreSQL interfaces list > > have a suggestion on how to RESET all session parameters? Seems we may > > need to add this feature in to the backend. > > with the oracle driver (i wrote) there is a neat thing in the > oci-libs: you have a server-handle _and_ a session handle. > the session handle sits "on" the server-handle and keeps > _all_ session specific data, the server handle "only" carries > the pure connection to oracle. so i keep the server handle > persistent and allocate/free session handles on it for each > request to PHP. that way the sessions are always clean. but i > also do a forces rollback on the session handle before i free > it on request-end so that in case of a script error all > outstanding transactions are rolled-back. Yes, it would be nice if we had that feature. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Any reasons there could't be either more statuses for a connection besides CONNECTION_BAD, CONNECTION_OK, or alternativetely, a new libpq function that could tell us READY_TO_RECEIVE_MORE_SQL_COMMANDS, PREVIOUS_TRANSACTION_ABORTED_UNTIL_..., etc. (Sorry that I'm shouting... I just write constant names that way like everyone else.) -- Jouni On Mon, 12 Feb 2001, Bruce Momjian wrote: > > > We discussed using 'ROLLBACK' before passing a connection to a new user, > > > but the problem was that ROLLBACK with no open transaction causes a > > > server log error message. We discussed adding 'ROLLBACK SILENT' to fix > > > this, but I believe a better, more portable solution is a simple "BEGIN > > > WORK;ROLLBACK". This will do nothing if there is no open transaction, > > > and will ROLLBACK any open transaction. I propose this be sent by PHP > > > as the first query when passing persistent connections. > > > > i'll have a look at that tomorrow (if my family allows;-). if > > "BEGIN WORK;ROLLBACK" does not stack transactions i think you > > might have found the solution to the php-postgres problem! do > > If we every get nested transactions, this will no longer work, but we > don't have them, and will not for a while. > > > you know how other script-interfaces (perl) to postgres > > handle the very same thing? > > They don't handle them, but our Java interface just added this feature. > > > > > As far as SET changes, does anyone on the PostgreSQL interfaces list > > > have a suggestion on how to RESET all session parameters? Seems we may > > > need to add this feature in to the backend. > > > > with the oracle driver (i wrote) there is a neat thing in the > > oci-libs: you have a server-handle _and_ a session handle. > > the session handle sits "on" the server-handle and keeps > > _all_ session specific data, the server handle "only" carries > > the pure connection to oracle. so i keep the server handle > > persistent and allocate/free session handles on it for each > > request to PHP. that way the sessions are always clean. but i > > also do a forces rollback on the session handle before i free > > it on request-end so that in case of a script error all > > outstanding transactions are rolled-back. > > Yes, it would be nice if we had that feature. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > -- > PHP Development Mailing List <http://www.php.net/> > To unsubscribe, e-mail: php-dev-unsubscribe@lists.php.net > For additional commands, e-mail: php-dev-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net >
> At 14:12 07/02/01 -0500, Bruce Momjian wrote: > >Thies, we talked at LinuxWorld about improving the reliability of > >persistent PostgreSQL connections in PHP. > > > >I believe the problem is that persistent connections sometime do not > >pass clean sessions because of open transactions and SET changes to the > >session. > > > >We discussed using 'ROLLBACK' before passing a connection to a new user, > >but the problem was that ROLLBACK with no open transaction causes a > >server log error message. We discussed adding 'ROLLBACK SILENT' to fix > >this, but I believe a better, more portable solution is a simple "BEGIN > >WORK;ROLLBACK". This will do nothing if there is no open transaction, > >and will ROLLBACK any open transaction. I propose this be sent by PHP > >as the first query when passing persistent connections. > > > >As far as SET changes, does anyone on the PostgreSQL interfaces list > >have a suggestion on how to RESET all session parameters? Seems we may > >need to add this feature in to the backend. > > Some Java apps utilise connection pools so they would possibly benefit. I > could add the BEGIN WORK;ROLLBACK; to JDBC. I don't think that belongs in jdbc. It is the connection pooling code that should execute those statements before passing over a new connection. No reason to have it for all jdbc users. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
> I don't think that belongs in jdbc. It is the connection pooling code
> that should execute those statements before passing over a new
> connection. No reason to have it for all jdbc users.
Well, I think most connection pools people use are database independent -
when it comes to JDBC this is the case at least.
regards,
Gunnar
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > > > I don't think that belongs in jdbc. It is the connection pooling code > > that should execute those statements before passing over a new > > connection. No reason to have it for all jdbc users. > > Well, I think most connection pools people use are database independent - > when it comes to JDBC this is the case at least. So what platform-indendent method do they use to see that the passed connection doesn't have an open transaction or some other setting? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Well, I think most connection pools people use are database independent -
> > when it comes to JDBC this is the case at least.
>
> So what platform-indendent method do they use to see that the passed
> connection doesn't have an open transaction or some other setting?
>
None - it is application responsibility to not mess around with the
connections. I usually use the Command pattern from the GoF book to do my
database access. This ensures me that I have one pluggable executor that
can do all the database specific stuff, like proper pre command exec
actions, post command exec actions, exeception handling(Error codes would
be very welcome in pgsql).
Cheers,
Gunnar