PHP :: Bug #25404 :: open transactions not closed when script ends

 [2003-09-06 11:20 UTC] mike at ecommerce dot com

Description:
------------
There is a problem with transactions which occurrs when 
accessing a PostgreSQL database using persistent 
connections. Please note that this problem does only occur 
when using persistent connections, non-persistent 
connection work fine,  
This problem becomes evident at the following situation: 
During an transaction, if some statement causes an error 
(e.g. wrong syntax), PostgreSQL does not allow any other 
commands until the transaction ends (causes this error 
message: current transaction is aborted, queries ignored 
until end of transaction block). That is correct 
behaviour, but the transaction should automatically be 
rollbacked by PHP when the script ends, so the next script 
which gets the connection link assigned can use it without 
problem. 
but this is not the case. the attached code piece 
reproduces the problem. the first time it gets executed, 
it runs fine, telling the correct error message. but the 
second time and all following times (assuming it gets the 
same persistent connection link again), it tells "the 
current transaction is aborted, queries ignored until end 
of transaction block". 
that means the transaction started at the first invocation 
did not get closed, and causes an error at the next 
invocation of the script. that is a pretty interesting 
effect - the script causing the error and the script 
showing the error are not necessary related - surely hard 
to debug in a full featured application :) 
Usually, any transactions that are open when the script 
ends should be closed automatically. According to what i 
could find out by looking at the cvs repository, support 
for this was added in PHP 4.0.5: 
http://cvs.php.net/diff.php/php-src/ext/pgsql/pgsql.c?login=2&r1=1.86&r2=1.87&ty=h&php=547f470b787a347436cc9b26811591e4 
However, i guess this code does not work for transactions 
that have been aborted due to an error in a statement (or 
it does not work at all - which i do not hope). 
the problem description ends here, now a suggested fix: 
i made some investigation into the source code of 
ext/pgsql/pgsql.c, and it seems like the problem goes away 
by splitting the line that says: 
res = PQexec(link,"BEGIN;ROLLBACK;"); 
into three lines: 
res = PQexec(link,"BEGIN;"); 
PQclear(res); 
res = PQexec(link,"ROLLBACK;"); 
by executing the attached test script it can be tested 
that the problem goes away after changing this. i do not 
know for sure, but i guess that postgresql "optimizes"  
execution of multiple commands by stopping at the first 
statement that fails. That means, BEGIN fails because 
another transaction is already open, and ROLLBACK is not 
executed thereof. however, if BEGIN and ROLLBACK are 
executed one by one, it works. 
ps: here is my configuration: 
postgresql 7.3.4 (both client libraries & server) 
php 4.3.3 configured with: 
'./configure' '--with-apxs=/usr/sbin/apxs' 
'--with-config-file-path=/etc/httpd/conf' 
'--enable-mbstring' '-enable-mbstr-enc-trans' 
'--enable-mbregex' '--with-xml' '--enable-wddx' 
'--with-gettext' '--with-mysql' '--with-pgsql'  
'--with-zlib' '--with-curl' '--enable-mime-magic' 
'--enable-sockets' '--enable-bcmath' '--enable-ftp' 
Reproduce code:
---------------
$conn = pg_pconnect("host=localhost port=5432 dbname=db user=usr password=pwd");
if (!$conn)
    die('database connection failed');
// var-dump the connection (should tell us its persistent link)
var_dump($conn); echo "<br>";
// begin a transaction
if (!@pg_query($conn, "BEGIN;"))
    die(pg_last_error($conn));
// execute a query that causes an error
if (!@pg_query($conn, "select 1 xxfdsaf fdshadfj sdfhj;"))
    die(pg_last_error($conn));
pg_close($conn); // its a persistent link, closing it should have no effect (but good practice to do it anyway :)
Expected result:
----------------
resource(1) of type (pgsql link persistent)  
ERROR: parser: parse error at or near "xxfdsaf" at 
character 10 
Actual result:
--------------
the first run is the expected result: 
resource(1) of type (pgsql link persistent)  
ERROR: parser: parse error at or near "xxfdsaf" at 
character 10 
however, starting from the second run it shows: 
resource(1) of type (pgsql link persistent)  
ERROR: current transaction is aborted, queries ignored 
until end of transaction block 

 [2003-09-06 11:30 UTC] helly@php.net

This bug has been fixed in CVS.
In case this was a PHP problem, snapshots of the sources are packaged
every three hours; this change will be in the next snapshot. You can
grab the snapshot at http://snaps.php.net/.
In case this was a documentation problem, the fix will show up soon at
http://www.php.net/manual/.
In case this was a PHP.net website problem, the change will show
up on the PHP.net site and on the mirror sites in short time.
Thank you for the report, and for helping us make PHP better.