Re: PHP and PostgreSQL - Mailing list pgsql-hackers

It was obviously designed with MySQL's "Nobody needs transactions for
webwork" type of situation in mind.

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