Thread: BUG #5244: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs
BUG #5244: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs
From
"Philip Graham"
Date:
The following bug has been logged online: Bug reference: 5244 Logged by: Philip Graham Email address: philip@lightbox.org PostgreSQL version: 8.3.8 Operating system: Linux Description: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs Details: This may be a PHP so please excure me if it is. <?php $pdo = new PDO('pgsql:host=localhost;dbname=a_db', 'a_user', 'my_pass'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo 'Creating test objects'."\n"; $pdo->query('CREATE SEQUENCE test_seq'); echo 'Setup complete'."\n"; $pdo->beginTransaction(); try { echo 'Setting savepoint'."\n"; $pdo->query('SAVEPOINT pre_id_fetch'); echo 'Fetching value'."\n"; $stmt = $pdo->query('SELECT currval(\'test_seq\');'); $curId = $stmt->fetchColumn(); echo 'Releasing savepoint'."\n"; $pdo->query('RELEASE SAVEPOINT pre_id_fetch'); } catch (PDOException $e) { echo 'Rolling back'."\n"; $pdo->query('ROLLBACK TO pre_id_fetch'); $curId = 0; } echo 'Cur Id: ',$curId,"\n"; Running this code it hangs after echoing 'Rolling back', but only hangs every other execution (assuming the sequence was deleted first). Thanks for any help, Philip
Re: BUG #5244: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs
From
Robert Haas
Date:
On Mon, Dec 14, 2009 at 11:15 PM, Philip Graham <philip@lightbox.org> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05244 > Logged by: =A0 =A0 =A0 =A0 =A0Philip Graham > Email address: =A0 =A0 =A0philip@lightbox.org > PostgreSQL version: 8.3.8 > Operating system: =A0 Linux > Description: =A0 =A0 =A0 =A0Attempting to rollback to a savepoint after r= eceiving an > error with state 55000 the process hangs > Details: > > This may be a PHP so please excure me if it is. > > <?php > $pdo =3D new PDO('pgsql:host=3Dlocalhost;dbname=3Da_db', 'a_user', 'my_pa= ss'); > $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > > echo 'Creating test objects'."\n"; > $pdo->query('CREATE SEQUENCE test_seq'); > > echo 'Setup complete'."\n"; > $pdo->beginTransaction(); > > try { > =A0 =A0echo 'Setting savepoint'."\n"; > =A0 =A0$pdo->query('SAVEPOINT pre_id_fetch'); > =A0 =A0echo 'Fetching value'."\n"; > =A0 =A0$stmt =3D $pdo->query('SELECT currval(\'test_seq\');'); > =A0 =A0$curId =3D $stmt->fetchColumn(); > =A0 =A0echo 'Releasing savepoint'."\n"; > =A0 =A0$pdo->query('RELEASE SAVEPOINT pre_id_fetch'); > } catch (PDOException $e) { > =A0 =A0echo 'Rolling back'."\n"; > =A0 =A0$pdo->query('ROLLBACK TO pre_id_fetch'); > =A0 =A0$curId =3D 0; > } > > echo 'Cur Id: ',$curId,"\n"; > > > Running this code it hangs after echoing 'Rolling back', but only hangs > every other execution (assuming the sequence was deleted first). I can't reproduce this using psql. Could you try? I am guessing that PHP is doing something funky, but I'm not really sure what. I do notice that you don't seem to have an endTransaction() or similar to match the beginTransaction() - could that be relevant? ...Robert
Re: BUG #5244: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs
From
Robert Haas
Date:
On Tue, Dec 15, 2009 at 11:18 PM, Philip Graham <philip@lightbox.org> wrote: > Robert Haas wrote: >> >> On Mon, Dec 14, 2009 at 11:15 PM, Philip Graham <philip@lightbox.org> >> wrote: >> >>> >>> The following bug has been logged online: >>> >>> Bug reference: =A0 =A0 =A05244 >>> Logged by: =A0 =A0 =A0 =A0 =A0Philip Graham >>> Email address: =A0 =A0 =A0philip@lightbox.org >>> PostgreSQL version: 8.3.8 >>> Operating system: =A0 Linux >>> Description: =A0 =A0 =A0 =A0Attempting to rollback to a savepoint after= receiving >>> an >>> error with state 55000 the process hangs >>> Details: >>> >>> This may be a PHP so please excure me if it is. >>> >>> <?php >>> $pdo =3D new PDO('pgsql:host=3Dlocalhost;dbname=3Da_db', 'a_user', 'my_= pass'); >>> $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); >>> >>> echo 'Creating test objects'."\n"; >>> $pdo->query('CREATE SEQUENCE test_seq'); >>> >>> echo 'Setup complete'."\n"; >>> $pdo->beginTransaction(); >>> >>> try { >>> =A0 echo 'Setting savepoint'."\n"; >>> =A0 $pdo->query('SAVEPOINT pre_id_fetch'); >>> =A0 echo 'Fetching value'."\n"; >>> =A0 $stmt =3D $pdo->query('SELECT currval(\'test_seq\');'); >>> =A0 $curId =3D $stmt->fetchColumn(); >>> =A0 echo 'Releasing savepoint'."\n"; >>> =A0 $pdo->query('RELEASE SAVEPOINT pre_id_fetch'); >>> } catch (PDOException $e) { >>> =A0 echo 'Rolling back'."\n"; >>> =A0 $pdo->query('ROLLBACK TO pre_id_fetch'); >>> =A0 $curId =3D 0; >>> } >>> >>> echo 'Cur Id: ',$curId,"\n"; >>> >>> >>> Running this code it hangs after echoing 'Rolling back', but only hangs >>> every other execution (assuming the sequence was deleted first). >>> >> >> I can't reproduce this using psql. Could you try? =A0I am guessing that >> PHP is doing something funky, but I'm not really sure what. =A0I do >> notice that you don't seem to have an endTransaction() or similar to >> match the beginTransaction() - could that be relevant? >> > > I also can't reproduce using psql, I'm suspecting it's a PHP thing. =A0If= I > use phpPgAdmin to watch what the connection is doing, when the script han= gs > it loops through: > > 1. ROLLBACK TO pre_fetch_id; > 2. DEALLOCATE pdo_stmt_<someGeneratedId>; > 3. <idle in transaction> > > <someGeneratedId> is always the same. > > I'm going to post the bug with PHP and see if I can get help there. Yeah, it sounds like PHP is magically inserting some SQL calls, but I don't understand it enough to know why or what to do about it. ...Robert
Re: BUG #5244: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs
From
Philip Graham
Date:
Robert Haas wrote: > On Mon, Dec 14, 2009 at 11:15 PM, Philip Graham <philip@lightbox.org> wrote: > >> The following bug has been logged online: >> >> Bug reference: 5244 >> Logged by: Philip Graham >> Email address: philip@lightbox.org >> PostgreSQL version: 8.3.8 >> Operating system: Linux >> Description: Attempting to rollback to a savepoint after receiving an >> error with state 55000 the process hangs >> Details: >> >> This may be a PHP so please excure me if it is. >> >> <?php >> $pdo = new PDO('pgsql:host=localhost;dbname=a_db', 'a_user', 'my_pass'); >> $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); >> >> echo 'Creating test objects'."\n"; >> $pdo->query('CREATE SEQUENCE test_seq'); >> >> echo 'Setup complete'."\n"; >> $pdo->beginTransaction(); >> >> try { >> echo 'Setting savepoint'."\n"; >> $pdo->query('SAVEPOINT pre_id_fetch'); >> echo 'Fetching value'."\n"; >> $stmt = $pdo->query('SELECT currval(\'test_seq\');'); >> $curId = $stmt->fetchColumn(); >> echo 'Releasing savepoint'."\n"; >> $pdo->query('RELEASE SAVEPOINT pre_id_fetch'); >> } catch (PDOException $e) { >> echo 'Rolling back'."\n"; >> $pdo->query('ROLLBACK TO pre_id_fetch'); >> $curId = 0; >> } >> >> echo 'Cur Id: ',$curId,"\n"; >> >> >> Running this code it hangs after echoing 'Rolling back', but only hangs >> every other execution (assuming the sequence was deleted first). >> > > I can't reproduce this using psql. Could you try? I am guessing that > PHP is doing something funky, but I'm not really sure what. I do > notice that you don't seem to have an endTransaction() or similar to > match the beginTransaction() - could that be relevant? > > ...Robert > I also can't reproduce using psql, I'm suspecting it's a PHP thing. If I use phpPgAdmin to watch what the connection is doing, when the script hangs it loops through: 1. ROLLBACK TO pre_fetch_id; 2. DEALLOCATE pdo_stmt_<someGeneratedId>; 3. <idle in transaction> <someGeneratedId> is always the same. I'm going to post the bug with PHP and see if I can get help there. Thanks, Philip
Re: BUG #5244: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs
From
Mark Kirkwood
Date:
Philip Graham wrote: > The following bug has been logged online: > > Bug reference: 5244 > Logged by: Philip Graham > Email address: philip@lightbox.org > PostgreSQL version: 8.3.8 > Operating system: Linux > Description: Attempting to rollback to a savepoint after receiving an > error with state 55000 the process hangs > Details: > > This may be a PHP so please excure me if it is. > > <?php > $pdo = new PDO('pgsql:host=localhost;dbname=a_db', 'a_user', 'my_pass'); > $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > > echo 'Creating test objects'."\n"; > $pdo->query('CREATE SEQUENCE test_seq'); > > echo 'Setup complete'."\n"; > $pdo->beginTransaction(); > > try { > echo 'Setting savepoint'."\n"; > $pdo->query('SAVEPOINT pre_id_fetch'); > echo 'Fetching value'."\n"; > $stmt = $pdo->query('SELECT currval(\'test_seq\');'); > $curId = $stmt->fetchColumn(); > echo 'Releasing savepoint'."\n"; > $pdo->query('RELEASE SAVEPOINT pre_id_fetch'); > } catch (PDOException $e) { > echo 'Rolling back'."\n"; > $pdo->query('ROLLBACK TO pre_id_fetch'); > $curId = 0; > } > > echo 'Cur Id: ',$curId,"\n"; > > > Running this code it hangs after echoing 'Rolling back', but only hangs > every other execution (assuming the sequence was deleted first). > > I think you need to be using $pdo->exec instead of $pdo->query for everything *except* the SELECT operation. The query method is really only intended for statements returning rows. Making the indicated changes stops the hang for me (Php 5.3.2) regards Mark