Thread: problem--pg_connect() and odbc_connect() return the same connection

I was having problems because pg_connect() (and odbc_connect()), when
given the same connection credentials, return the SAME IDENTICAL
connection.

Given this, it is difficult to write functions that use a database
connection but do not have  unwanted side effects.

* If function b() does anything with the database, function a() cannot
call function b() while a() is inside a transaction of some sort if b()
uses transactions (this would prematurely commit or rollback a()'s
transaction).

* If b() has an database error, a()'s transaction will still be
automatically rolled back.

* Even if a() does not use transactions, if b() returns without
committing or rolling back his transaction, he will leave a() inside of
a transaction that a() is not prepared to issue a commit for.  If this
is eventually rolled back then all of a()'s changes are lost even though
a() did not have an error.

* b() cannot change to transaction isolation level SERIALIZABLE without
either committing or rolling back a()'s transaction.

By careful analysis of both b() and a(), I can usually prevent this sort
of clash, but it makes it impossible to write a function b() that can
safely be called by a() at any point in a()'s execution.  In a large
project, this leads to compiling a list of functions and labelling them
as "doesnt use database", "uses database", and "uses database and
transactions", and things get complicated quickly.

It seems to me that much of the transaction handling, and the (ACIDity
therein) of postgresql is lost because of this "feature" that multiple
connection requests with the same parameters return the same exact
connection.

How do you web developers out there deal with this?  Do you have some
elegant workaround for this?

Also, is there anyone on this list who works on the PHP pg_xxxxx
extension?   Has this issue ever been brought up before?

Regards,

Paul Tillotson
username "ptchristendom" at the domain of yahoo dot com

pntil@shentel.net wrote:
> I was having problems because pg_connect() (and odbc_connect()), when
> given the same connection credentials, return the SAME IDENTICAL
> connection.
>
> Given this, it is difficult to write functions that use a database
> connection but do not have  unwanted side effects.
> ...
> It seems to me that much of the transaction handling, and the (ACIDity
> therein) of postgresql is lost because of this "feature" that multiple
> connection requests with the same parameters return the same exact
> connection.
>
> How do you web developers out there deal with this?  Do you have some
> elegant workaround for this?
>
> Also, is there anyone on this list who works on the PHP pg_xxxxx
> extension?   Has this issue ever been brought up before?

I think in many cases we wouldn't want a single script to open multiple
connections to PostgreSQL due to the per-connection overhead and limited
number of backends available.  But if you really want to, there is a way
with the PostgreSQL extension:

   $conn_handle = pg_connect($connstring, PGSQL_CONNECT_FORCE_NEW);

This gives you a fresh new connection regardless of any existing connection
which used the same connection info string $connstring.  This feature was
added in PHP-4.3.0, so make sure you are running at least that version. It
hasn't really made the documentation, so it might be considered experimental.
But it works.