Skip site navigation (1) Skip section navigation (2)

Re: PHP and PostgreSQL

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Rod Taylor <rbt(at)zort(dot)on(dot)ca>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: PHP and PostgreSQL
Date: 2001-01-02 03:57:38
Message-ID: 200101020357.WAA06712@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces
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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In response to

Responses

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2001-01-02 03:59:45
Subject: RE: PHP and PostgreSQL
Previous:From: Bruce MomjianDate: 2001-01-02 03:56:52
Subject: Re: PHP and PostgreSQL

pgsql-interfaces by date

Next:From: Christopher Kings-LynneDate: 2001-01-02 03:59:45
Subject: RE: PHP and PostgreSQL
Previous:From: Bruce MomjianDate: 2001-01-02 03:56:52
Subject: Re: PHP and PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group