Обсуждение: 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

От
"Philip Graham"
Дата:
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

От
Robert Haas
Дата:
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

От
Robert Haas
Дата:
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

От
Philip Graham
Дата:
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

От
Mark Kirkwood
Дата:
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