Re: Fun with DataObject, Transactions, PostgreSQL and Sequences

Hi, Markus Wolff wrote:

LOG: statement: CREATE SEQUENCE pagesize_seq ERROR: current transaction is aborted, commands ignored until end of transaction block </SNIP> I have no clue why the "begin;" is coming *after* the first call to NEXTVAL, as $do->query('BEGIN'); is actually the first thing I do after creating the first DataObject. Anyway, cross-checking with psql shows that it seems to be very likely that Postgres has some problems with using DDL statements (such as CREATE SEQUENCE) within transactions. Leave out the transaction and all is good, all is well and everyone lived happily everafter.

In fact PostgreSQL doesn't have problems with DDL in transactions. It has problems with errors in transactions (as the log above suggests): if there is an error then the whole transaction is effectively rolled back. Since you are using 8.0 you may consider using SAVEPOINT before calling nextval() and ROLLBACK TO SAVEPOINT in case of non-existant sequence, in that case only the part of the transaction will be rolled back.