From: Rod Taylor <rbt@zort.on.ca>
To: pgsql-hackers@postgresql.org
Subject: Re: PHP and PostgreSQL
Date: Wed, 27 Dec 2000 10:09:59 -0500
Message-ID: <02a101c07017$5ab64fb0$6500000a@jester> (raw)
References: <183FA749499ED311B6550000F87E206C0C94D1@srv.ctlno.com>
> 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.
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: rbt@zort.on.ca, pgsql-hackers@postgresql.org
Subject: Re: PHP and PostgreSQL
In-Reply-To: <02a101c07017$5ab64fb0$6500000a@jester>
* 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