Re: PHP and PostgreSQL

From: "Rod Taylor" <rbt(at)zort(dot)on(dot)ca>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: PHP and PostgreSQL
Date: 2000-12-27 15:09:59
Message-ID: 02a101c07017$5ab64fb0$6500000a@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-interfaces

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message mlw 2000-12-27 15:15:42 Re: PHP and PostgreSQL
Previous Message Tom Lane 2000-12-27 15:08:29 Re: [HACKERS] Re: Tuple-valued datums on Alpha (was Re: 7.1 on DEC/Alpha)

Browse pgsql-interfaces by date

  From Date Subject
Next Message mlw 2000-12-27 15:15:42 Re: PHP and PostgreSQL
Previous Message Daniele Orlandi 2000-12-27 14:39:14 Re: PHP and PostgreSQL