Fun with DataObject, Transactions, PostgreSQL and Sequences

Hi there, I'm using DataObject (1.7.15) with PostgreSQL (8.0.1) now for the first time and I've encountered some strange phenomenon. I have two DataObjects where I first look into the database to see if a record with the given parameters already exists. If it doesn't, it is to be created. The database is in virgin state, so there is no data and also the sequences for the tables do not exist yet. All operations are encapsulated within one transaction. So far, so good. The first table is called "language". Everything goes as planned: <SNIP> Language: QUERY: SELECT * FROM language WHERE language.name = 'German' [...I have added a bit of my own debug info, so the following deviates a bit from the standard DO debug output...] [db_error: message="DB Error: no such table" code=-18 mode=return level=notice prefix="" info="SELECT NEXTVAL('language_seq') [nativecode=ERROR: relation "language_seq" does not exist]"] Creating sequence...SeqName format is: %s_seq FINAL SEQNAME: language_seq NEXTVAL: 1 Language: QUERY: INSERT INTO language (language_id , name ) VALUES ( 1 , 'German' ) Language: query: QUERY DONE IN 0.033642053604126 seconds </SNIP> As expected, an error is thrown on the first call to NEXTVAL, as the sequence doesn't exist yet. The sequence is then created and the INSERT is performed. Great! Now, the next table, called "pagesize": <SNIP> Pagesize: QUERY: SELECT * FROM pagesize WHERE pagesize.name = 'zzz' [...] [db_error: message="DB Error: no such table" code=-18 mode=return level=notice prefix="" info="SELECT NEXTVAL('pagesize_seq') [nativecode=ERROR: relation "pagesize_seq" does not exist]"] Creating sequence...SeqName format is: %s_seq FINAL SEQNAME: pagesize_seq BOOM! [db_error: message="DB Error: unknown error" code=-1 mode=return level=notice prefix="" info="CREATE SEQUENCE pagesize_seq [nativecode=ERROR: current transaction is aborted, commands ignored until end of transaction block]"] </SNIP> Again, the first error is expected because of the non-existing sequence. But then DataObject tries to create the sequence again, but this time the creation fails with a not-too-helpful error message coming from PostgreSQL :-( Another strange thing is the output in PostgreSQL's query log: <SNIP> LOG: statement: SELECT * FROM language WHERE language.name = 'German' LOG: statement: SELECT NEXTVAL('language_seq') ERROR: relation "language_seq" does not exist LOG: statement: begin; LOG: statement: CREATE SEQUENCE language_seq LOG: statement: SELECT NEXTVAL('language_seq') LOG: statement: INSERT INTO language (language_id , name ) VALUES ( 1 , 'German' ) LOG: statement: SELECT * FROM pagesize WHERE pagesize.name = 'zzz' LOG: statement: SELECT NEXTVAL('pagesize_seq') ERROR: relation "pagesize_seq" does not exist LOG: statement: CREATE SEQUENCE pagesize_seq ERROR: current transaction is aborted, commands ignored until end of transaction block LOG: statement: abort; </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. Now, I don't know if this is a common drawback of PostgreSQL, but at least if I'm not the only one having this problem, it implies two things that should IMHO be done in DataObject: 1. Provide an option for the Generator to create all sequences for all generated tables in advance 2. Whenever a sequence is not found, if a transaction has been started before, do not attempt to create it. Simply raise an error so the developer is alerted, can do a rollback and create the sequence without unsing a transaction. After that, the transaction could be started anew. Does anyone have any more insight on how Postgres handles this? Wouldn't make much sense to change DataObject behaviour if I'm the only one having this problem. On the other hand, I believe there may be more DBMS out there not allowing DDL in transactions. Regards, Markus