ID: 25404
Updated by: [email]helly@php.net[/email]
Reported By: mike at ecommerce dot com
-Status: Open
+Status: Closed
Bug Type: PostgreSQL related
Operating System: Linux
PHP Version: 4.3.3
New Comment:

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 [url]http://snaps.php.net/[/url].

In case this was a documentation problem, the fix will show up soon at
[url]http://www.php.net/manual/[/url].

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.


Previous Comments:
------------------------------------------------------------------------

[2003-09-06 11:20:46] 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:
[url]http://cvs.php.net/diff.php/php-src/ext/pgsql/pgsql.c?login=2&r1=1.86&r2=1.87&ty=h&php=547f470b7 87a347436cc9b26811591e4[/url]


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



------------------------------------------------------------------------


--
Edit this bug report at [url]http://bugs.php.net/?id=25404&edit=1[/url]