|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2003-03-18 06:38 UTC] 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.
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Fri Oct 04 07:01:26 2024 UTC |
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.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++