pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL and PHP persistent connections
17+ messages / 7 participants
[nested] [flat]
* PostgreSQL and PHP persistent connections
@ 2001-02-07 19:12 Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 4 replies; 17+ messages in thread
From: Bruce Momjian @ 2001-02-07 19:12 UTC (permalink / raw)
To: Thies C. Arntzen <thies@digicol.de>; +Cc: Rasmus@candle.pha.pa.us, Lerdorf <rasmus@php.net>; pgsql-interfaces
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-07 19:56 Thies C. Arntzen <thies@thieso.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
3 siblings, 1 reply; 17+ messages in thread
From: Thies C. Arntzen @ 2001-02-07 19:56 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Thies C. Arntzen <thies@digicol.de>; Rasmus@candle.pha.pa.us, Lerdorf <rasmus@php.net>; pgsql-interfaces; php-dev@lists.php.net
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 think you
might have found the solution to the php-postgres problem! do
you know how other script-interfaces (perl) to postgres
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 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.
regards,
tc
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-07 20:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
3 siblings, 2 replies; 17+ messages in thread
From: Tom Lane @ 2001-02-07 20:49 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Thies C. Arntzen <thies@digicol.de>; Rasmus@candle.pha.pa.us, Lerdorf <rasmus@php.net>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-07 20:50 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2001-02-07 20:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-07 21:41 Peter Mount <peter@retep.org.uk>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
3 siblings, 1 reply; 17+ messages in thread
From: Peter Mount @ 2001-02-07 21:41 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Thies C. Arntzen <thies@digicol.de>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-07 22:07 Peter Mount <peter@retep.org.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 17+ messages in thread
From: Peter Mount @ 2001-02-07 22:07 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-08 13:10 Jan Wieck <janwieck@Yahoo.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Jan Wieck @ 2001-02-08 13:10 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
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 and eating 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Re: PostgreSQL and PHP persistent connections
@ 2001-02-08 14:18 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jan Wieck <janwieck@Yahoo.com>
0 siblings, 0 replies; 17+ messages in thread
From: Bruce Momjian @ 2001-02-08 14:18 UTC (permalink / raw)
To: Jan Wieck <janwieck@Yahoo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
> > > 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-09 13:41 Thies C. Arntzen <thies@thieso.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
3 siblings, 1 reply; 17+ messages in thread
From: Thies C. Arntzen @ 2001-02-09 13:41 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Thies C. Arntzen <thies@digicol.de>; Rasmus@candle.pha.pa.us, Lerdorf <rasmus@php.net>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-09 15:19 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Thies C. Arntzen <thies@thieso.net>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2001-02-09 15:19 UTC (permalink / raw)
To: Thies C. Arntzen <thies@thieso.net>; +Cc: Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
> > 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-02-09 15:39 Thies C. Arntzen <thies@thieso.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Thies C. Arntzen @ 2001-02-09 15:39 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Re: PostgreSQL and PHP persistent connections
@ 2001-02-12 17:25 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Thies C. Arntzen <thies@thieso.net>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2001-02-12 17:25 UTC (permalink / raw)
To: Thies C. Arntzen <thies@thieso.net>; +Cc: Thies C. Arntzen <thies@digicol.de>; Rasmus@candle.pha.pa.us, Lerdorf <rasmus@php.net>; pgsql-interfaces; php-dev@lists.php.net
> > 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PHP-DEV] Re: Re: PostgreSQL and PHP persistent connections
@ 2001-02-12 23:19 Jouni Ahto <jah@php.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Jouni Ahto @ 2001-02-12 23:19 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Thies C. Arntzen <thies@thieso.net>; Thies C. Arntzen <thies@digicol.de>; Rasmus@candle.pha.pa.us, Lerdorf <rasmus@php.net>; pgsql-interfaces; php-dev@lists.php.net
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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: PostgreSQL and PHP persistent connections
@ 2001-03-19 22:01 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Mount <peter@retep.org.uk>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2001-03-19 22:01 UTC (permalink / raw)
To: Peter Mount <peter@retep.org.uk>; +Cc: Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [JDBC] Re: PostgreSQL and PHP persistent connections
@ 2001-03-19 22:38 Gunnar R|nning <gunnar@candleweb.no>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Gunnar R|nning @ 2001-03-19 22:38 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Peter Mount <peter@retep.org.uk>; Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [JDBC] Re: PostgreSQL and PHP persistent connections
@ 2001-03-19 23:54 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Gunnar R|nning <gunnar@candleweb.no>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2001-03-19 23:54 UTC (permalink / raw)
To: Gunnar R|nning <gunnar@candleweb.no>; +Cc: Peter Mount <peter@retep.org.uk>; Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [JDBC] Re: PostgreSQL and PHP persistent connections
@ 2001-03-20 11:59 Gunnar R|nning <gunnar@candleweb.no>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Gunnar R|nning @ 2001-03-20 11:59 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Gunnar R|nning <gunnar@candleweb.no>; Peter Mount <peter@retep.org.uk>; Thies C. Arntzen <thies@digicol.de>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2001-03-20 11:59 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-07 19:12 PostgreSQL and PHP persistent connections Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-07 19:56 ` Thies C. Arntzen <thies@thieso.net>
2001-02-12 17:25 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-12 23:19 ` Jouni Ahto <jah@php.net>
2001-02-07 20:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-07 20:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-08 13:10 ` Jan Wieck <janwieck@Yahoo.com>
2001-02-08 14:18 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-07 22:07 ` Peter Mount <peter@retep.org.uk>
2001-02-07 21:41 ` Peter Mount <peter@retep.org.uk>
2001-03-19 22:01 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-19 22:38 ` Gunnar R|nning <gunnar@candleweb.no>
2001-03-19 23:54 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-20 11:59 ` Gunnar R|nning <gunnar@candleweb.no>
2001-02-09 13:41 ` Thies C. Arntzen <thies@thieso.net>
2001-02-09 15:19 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-09 15:39 ` Thies C. Arntzen <thies@thieso.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox