note 46799 deleted from function.pg-last-oid by tomsommer
| From: | tomsommer@php.net | Date: | Sun, 24 Oct 2004 14:24:57 +0000 |
| Subject: | note 46799 deleted from function.pg-last-oid by tomsommer | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-79027@lists.php.net to get a copy of this message | ||
Note Submitter: Jaimie Sirovich
----
The user is correct. There is no race condition. I use the following code all the time:
function insert($table_name, $info, $key_name = 'id', $seq_name = null)
{
$names = "";
$values = "";
$sizeof_info = sizeof($info);
$cnt = 0;
if (!$seq_name)
$seq_name = $table_name . "_id_seq";
$q = "SELECT nextval('$seq_name'::text)";
$id = DB::queryOneVal($q, 'nextval');
$info[$key_name] = $id;
$sizeof_info = sizeof($info);
foreach ($info as $name => $val) {
$names .= pg_escape_string($name);
$values .= "'" . pg_escape_string($val) . "'";
if ($cnt < ($sizeof_info - 1)) {
$names .= ",";
$values .= ",";
}
$cnt++;
}
$q = "INSERT INTO $table_name ($names) VALUES ($values)";
$tmp = DB::queryAffected($q);
return ($tmp != -1) ? $id : -1;
}
No, the user is correct. when you do a 'select nextval' from a sequence, that increments
the sequence. If someone else does a 'select nextval' or inserts into the table with a
'null' value for the field defaulted to a nextval, they will get the next value- thus if
another record is inserted after the nextval and before the insert, that next record will still get
a different value from the sequence.
Am I wrong?
[Editor's Note: If another record is inserted after the nextval is obtained and before you the
INSERT query this code will fail. This should not be done on busy sites.]
Especially now with an optional oid field, getting an implicit serial key is harder than ever. The
solution is to get the serial key first, and then use that value in an insert:
<?
pg_query($conn, 'CREATE TABLE foo (key SERIAL, foo TEXT)');
$res=pg_query("SELECT nextval('foo_key_seq') as key");
$key=pg_fetch_array($res, 0);
$key=$key[key];
// now we have the serial value in $key, let's do the insert
pg_query("INSERT INTO foo (key, foo) VALUES ($key, 'blah blah')");
?>
Hope this helps...