KO3, PostgresSQL, Transactions, and PDOException

Question

I've run into an issue that I'm hoping to get a little help on. I'm using the following:

Kohana 3.0.7
PostgreSQL 8.4
Transactions in PostgreSQL using

$db->query(NULL, 'BEGIN', FALSE)  
$db->query(NULL, 'ROLLBACK', FALSE);  
$db->query(NULL, 'COMMIT', FALSE);   

The issue is that when I send a query to the database that results in a postgres error within a transaction my system freezes up. When I send the same query to the database without wrapping it in a transaction the PDO error is reported back just as expected. Here is an exmaple:

This first example works fine and duplicate key value violates unique constraint "pk_test_table" error is returned:

$query = DB::query(Database::INSERT, 'INSERT INTO test_table (test_table_id, test_table_val) VALUES (:id, :value)';
$query->param(':id', 1);
$query->param(':value', "test value");

try 
{
    $result = $query->execute($db);
} 
catch (Exception $e) 
{
    echo 'Caught exception: ',  $e->getMessage(), "\n";
}           

This second example causes my system to freeze (I can't tell if it's an infinite loop, or some other freeze):

$db->query(NULL, 'BEGIN', FALSE);

$query = DB::query(Database::INSERT, 'INSERT INTO test_table (test_table_id, test_table_val) VALUES (:id, :value)';
$query->param(':id', 1);
$query->param(':value', "test value");

try 
{
    $result = $query->execute($db);
} 
catch (Exception $e) 
{
    echo 'Caught exception: ',  $e->getMessage(), "\n";
}           

$db->query(NULL, 'ROLLBACK', FALSE);

As you can see the only difference is that the second example is wrapped in a transaction.

Any ideas on what is going on? Any suggestions for things to try?

Answer 1

I found a way to work around the issue. Not sure if this is a bug in PDO or some other part of the tool set but what I'm doing to work around is the following:

$exception_exists = FALSE;

$db->query(NULL, 'BEGIN', FALSE);

$query = DB::query(Database::INSERT, 'INSERT INTO test_table (test_table_id, test_table_val) VALUES (:id, :value)';
$query->param(':id', 1);
$query->param(':value', "test value");

try 
{
    $result = $query->execute($db);
} 
catch (Exception $e) 
{
    echo 'Caught exception: ',  $e->getMessage(), "\n";
    $exception_exists = TRUE;
}           

if (!$exception_exists)
{
    $db->query(NULL, 'ROLLBACK', FALSE);
}

By adding the variable $exception_exists in the catch I can then act of that if there is no exception. If there is an exception and I try to ROLLBACK or COMMIT then I get the freezing behavior.

This works for now but I wouldn't call it elegant.