#22761 [Ana->Bgs]: nextId does not create the sequence within transaction

ID: 22761 Updated by: cox@php.net Reported By: zubchenko at alarit dot com -Status: Analyzed +Status: Bogus Bug Type: PEAR related Operating System: Linux PHP Version: 4.3.0 Assigned To: ssb New Comment: This is a documentation problem. We can't hang a transaction (and less even if you start it with $db->query('BEGIN'); instead of the DB methods), create the sequence and "unpause" the transaction. The best way to do it without manually create the sequence before, would be: <?php .. $e = $db->createSequence('foo'); if (DB::isError($e) && $e->getCode() != DB_ALREADY_EXISTS) { die("unable to create sequence " . $e->getUserInfo()); } // begin transaction and rest of code .. ?> Perhaps would be nice, if createSequence() does not return an error if the sequence already exist. FEATURE++ Previous Comments: ------------------------------------------------------------------------ [2003-04-30 06:50:55] lsmith@php.net i would also say that we leave things as they are an simply document this behavior accordingly. the auto creation of sequences is more a "lazy" programmers feature that really a core requirement of nextId and therefore we should not make the code even more complex for this "lazy" programmers feature. ------------------------------------------------------------------------ [2003-04-28 03:57:04] arnaud@php.net changing status (alan got me again ;) ------------------------------------------------------------------------ [2003-03-18 16:23:11] blair at squiz dot net This has to do with PostgreSQL's mannerism of abortting a transaction when an error occurs and the error occurs when the SELECT NEXTVAL('[seqname]'); gets called because the sequence does not exist. Then when the DB object attempts to create the sequence you get this msg : ERROR: current transaction is aborted, queries ignored until end of transaction block The only way I have found around this is to make DB::createSequence() calls in my install script. Anything else would mean making making a specific query for checking for each PostgreSQL version and checking that the sequence existed before calling it (I haven't found an easy way to check if a table/sequence exists). This could also be a performance hit when doing large data imports. ------------------------------------------------------------------------ [2003-03-18 06:38:17] zubchenko at alarit dot com We are using PostrgreSQL 7.3 with PHP 4.2.3 in our project. We have discovered that when calling nextId with name of non-existent sequence we get NULL as result without any error. In pgAdmin all is fine. It happens only within transaction. Here is example of code: //opening connection to DB with DB class $res = $db->query('BEGIN'); if (DB::isError($res)) { die(trigger_error($res->getMessage() ." : ".$res->getUserInfo(),E_USER_ERROR)); } debug($db->nextId('some_non_existing_seq')); $res = $db->query('COMMIT'); if (DB::isError($res)) { die(trigger_error($res->getMessage() ." : ".$res->getUserInfo(),E_USER_ERROR)); } //closing connection This code results in NULL output from debug and nothing else. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=22761&edit=1