RE: PHP and PostgreSQL

At my work we were happily cruising along using PHP/Postgres persistent
connections.  This was all happy until we installed Phorum.  Phorum has its
own database abstraction layer, quite separate to all our own database
classes.

Now what happens is if someone browses the phorums for a little while, and
then switches to pages that use our own db classes, you get random slowdowns
(ie. a select occurs, and then times out after 2 mins or so).

If you use psql, and perform the select you get a 'shared memory corruption'
and 'timed out waiting for lock' errors.

Our basic theory is that somehow locks aren't being properly released by
phorum, or the connection pooling is stuffed - although Phorum doesn't use
the LOCK or BEGIN/COMMIT commands.  Actually, we did notice that Phorum
issues a COMMIT whenever it closes a connection, even tho it never issues a
BEGIN.  If we avoid running any Phorum scripts - no problems occur.

In an attempt to fix the problem, we configured phplib and Phorum to use
non-persistent postgresql connections.  This worked fine, until now we have
a particular script, no different to the rest, that cannot acquire a
connection - it just gets an invalid resource handle.  We switched phplib
back to persistent connections, and it works fine again - nothing else was
changed!

So my advice to the PHP people is to just fix Postgres connection handling!

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Bruce Momjian
> Sent: Tuesday, January 02, 2001 11:58 AM
> To: Rod Taylor
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] PHP and PostgreSQL
>
>
> This is interesting.  I always wondered how the persistent connection
> stuff handled this, and not I see that it doesn't.
>
> [ Charset ISO-8859-1 unsupported, converting... ]
> > > The only problem we have run into (and I have heard of others
> having this
> > > problem also) is with persistent connections.  I have seen
> discussion on
> > > persistent connection problems but I'm not sure the problem was ever
> > > resolved.  The problem we have seen is that when using persistent
> > > connections the web server doesn't seen to reuse the connections or
> > somthing
> > > to that effect.  The result being that we eventually use up
> our postgres
> > > limit of 48 connections and nothing can connect to postgre
> anymore.  It is
> > > possible that this is a configuration problem that we haven't
> sufficiently
> > > investigated, but I meniton it because I have heard other
> talk of this.
> > > Anyone have more information?
> >
> > The *real* problem with persistent connections is:
> >
> > Script1:  BEGIN;
> > Script1:  UPDATE table set row = 'things';
> > Script2:  Insert into table (id) values ('bad data');
> > Script1: COMMIT;
> >
> > Since script2 managed to do a BAD insert in the middle of script1's
> > transaction, the transaction in script1 fails.  Obvious
> solution?  Don't do
> > connection sharing when a transaction is enabled.  The whole persistent
> > connection thing is only valid for mysql as it's the only thing
> that doesn't
> > really support transactions (and even thats partially changed).
> >
> > They need to look for stuff going through (keywords like BEGIN)
> and 'lock'
> > that connection to the single entity that opened it.
> >
> > It's much easier to write your own.  I wrote a few functions like:
> >
> > get_connection('DB PARMS');
> > begin_transaction();
> >
> > commit_transaction();
> > close_connection();
> >
> > All of this is done in a class which has knowledge of all
> connections that a
> > script is currently using.  Beginning a transaction locks down the
> > connection from use by any other handler, they're all bumped to
> another one.
> > Problem?  It requires atleast 1 connection per page, but since they're
> > actually dropped at close it's not so bad.
> >
> >
>
>
> --
>   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
>