Re: BUG #5244: Attempting to rollback to a savepoint after receiving an error with state 55000 the process hangs

On Tue, Dec 15, 2009 at 11:18 PM, Philip Graham <philip(at)lightbox(dot)org> wrote:
> Robert Haas wrote:
>>
>> On Mon, Dec 14, 2009 at 11:15 PM, Philip Graham <philip(at)lightbox(dot)org>
>> wrote:
>>
>>>
>>> The following bug has been logged online:
>>>
>>> Bug reference:      5244
>>> Logged by:          Philip Graham
>>> Email address:      philip(at)lightbox(dot)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?
>>
>
> 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.

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