From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Rod Taylor <rbt@zort.on.ca>
Cc: pgsql-hackers@postgresql.org
Subject: Re: PHP and PostgreSQL
Date: Mon, 1 Jan 2001 22:57:38 -0500 (EST)
Message-ID: <200101020357.WAA06712@candle.pha.pa.us> (raw)
In-Reply-To: <02a101c07017$5ab64fb0$6500000a@jester>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: pgman@candle.pha.pa.us, rbt@zort.on.ca, pgsql-hackers@postgresql.org
Subject: Re: PHP and PostgreSQL
In-Reply-To: <200101020357.WAA06712@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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