[fw-db] postgres transaction fails

Hi All,

I have a curious problem with a data import I'm doing into Postgres using
zend db. Might be a Postgres issue, but I'd like to ask here first for
opinions, in case I'm missing something. I'm reading a spreadsheet and using
Zend_Form to crunch each row (filter and validate) and then I'm using
Zend_Db_Table.save() to save the row. It works great as I'm avoiding writing
a single line of SQL.

So imagine I have 600 saves (INSERTs into postgres) to do in the code below,
and at attempt 234 we have an exception. Obviously you'd expect a rollback
of the last 233 INSERTs. Trouble is that my rollback is not working. It
seems to me that because the save() method calls a SELECT on the inserted
record immediately after insert, that the transaction block can no longer be
rolled back. Anyone seen this behaviour? I'm aware that there is an insert()
method that would bypass the extra SELECT performed by save(), but that
requires a bit of extra work to clean the data before inserting. I would not
have expected the $db->rollback() to fail anyway, but it does.

The postgres log shows something like this:

BEGIN
INSERT
SELECT
INSERT
SELECT
(repeat INSERT SELECT a few hundred times)
Error blah blah
ROLLBACK

But the ROLLBACK does not take effect. It's my windows development machine
and it's Posgres 8.3. See my simplified code snippet below.

regards,
Andy

*********
$db->beginTransaction();
$error = false;

for($i=0; $i<$rowCount; $i++)
{
try
{
$data = $rows[$i];

if($form->isValid($data))
{
$row = $table->createRow($data);
$ID = $row->save();
}
else
{
$error = true;
$db->rollBack();
break;
}

}
catch (Exception $e)
{
$error = true;
$db->rollBack();
break;
}
}

if(!$error) $db->commit();

--
View this message in context: http://www.nabble.com/postgres-transaction-fails-tp24567606p24567606.html
Sent from the Zend DB mailing list archive at Nabble.com.