Re: Concurrency and pg_getlastoid()

I just took a peek at the PHP source and the libpq source. The pg_getlastoid() function is merely a PHP interface to libpq's PQoidStatus() function. There is no need to worry about concurrency. You see, the oid is returned with the result when the query is an INSERT. This means that when you do this: $result = pg_exec($conn, "INSERT...."); $theoid = pg_getlastoid($result); the only thing PHP does is ask libpq for a value that is already in the $result. PHP/libpq does not ask the PostgreSQL database anything at all during the call to pg_getlastoid() as it already knows what it needs to know. Of course, the oid itself, though, is usually pretty meaningless in and of itself to the programmer. You will probably want to make a call to the database to get the value of the primary key of the newly inserted row. Like this (ummm, this is going **completely** from memory...note that it is untested...use as a guideline; and of course, put in some error checking!): $result = pg_exec($conn, "INSERT...."); $theoid = pg_getlastoid($result); pg_freeresult($result); $result = pg_exec($conn, "SELECT id FROM table WHERE oid = $theoid"); $temp = pg_fetch_row($result); $newkey = $temp[0]; This is how, for example, to get the value of a primary key that is automatically assigned with a DEFAULT NEXTVAL('some_sequence_name') kind of thing. One more time (to make sure it's clear) PHP already knows the oid if the inserted row immediately after the pg_exec() of the insert statement. The call to pg_getlastoid() merely gives it to you and doesn't require asking PostgreSQL anything. Hence, no concurrency problems. Even if seven hundred other INSERTs are done by different threads/processes/connections/whatever into the database between the time your INSERT happens and you call pg_getlastoid(), you will get the correct value. As long as you haven't done another pg_exec and stored the result set in the same variable, that is. Examples: WILL NOT WORK (because $result has changed after the INSERT): $result = pg_exec($conn, "INSERT ..."); $result = pg_exec($conn, "SELECT ..."); $theoid = pg_getlastoid($result); WILL WORK (even through the same database connection!!!!): $result = pg_exec($conn, "INSERT ..."); $differentresult = pg_exec($conn, "INSERT ..."); $theoid = pg_getlastoid($result); $thedifferentoid = pg_getlastoid($differentresult); Have fun! Doug At 09:18 PM 8/21/00 -0400, Ben Lanson wrote: >I am searching for the best way to enter data into a postgresql database >and return with the value of a serial type in the table into which I just >inserted. I am currently using locks to guarantee atomicity, but I am >looking for a better way. I currently have: > >BEGIN WORK >INSERT ... >SELECT currval(my_seq); >END WORK > >Is there a less tortured way to do this? I considered pg_getlastoid(), but >unless I have missed something in the documentation, this doesn't resolve >my concurrency issue. > >Ben