Thread: Re: [INTERFACES] PostgreSQL and PHP persistent connections

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 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