Re: Fun with DataObject, Transactions, PostgreSQL and Sequences

Alexey Borzov wrote:

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.

Yeah, in the meantime I've found a bit more information on the topic and it seems indeed that DDL within transactions is not the problem here. However, as an error can be *expected* when trying to access a fresh DB with NEXTVAL, my point remains that DO (or even PEAR::DB) should simply bail out with a meaningful error if a transaction has been started and an error occurs while issuing NEXTVAL. Also, being able to create all sequences in advance would be a big plus for transaction users. CU Markus