Re: PHP and PostgreSQL

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