phppostgresqlinsert-id

Postgresql and PHP: is the currval a efficent way to retrieve the last row inserted id, in a multiuser application?


Im wondering if the way i use to retrieve the id of the last row inserted in a postgresql table is efficent..

It works, obviously, but referencing on the serial sequence currval value could be problematic when i have many users adding rows in the same table at the same time.

My actual way is:

$pgConnection = pg_connect('host=127.0.0.1 dbname=test user=myuser password=xxxxx')or die('cant connect');

$insert = pg_query("INSERT INTO customer (name) VALUES ('blabla')");
$last_id_query = pg_query("SELECT currval('customer_id_seq')");
$last_id_results = pg_fetch_assoc($last_id_query);
print_r($last_id_results);
pg_close($pgConnection);

Well, its just a test atm. But anyway, i can see 3 issues with this way:

  1. Referencing on the customer_id_seq, if two user do the same thing in the same time, could happen that them both get the same id from that way... or not?
  2. I have to know the table's sequence name. Becose pg_get_serial_sequence dont works for me (im newbie on postgresql, probably is a configuration issue)

Any suggestion/better ways?

p.s: i can't use the PDO, becose seem lack a bit with the transaction savepoint; I wont use zend and, in the end, i'll prefer to use the php pg_* functions (maybe i'll build up my classes in the end)

EDIT:

@SpliFF(thet deleted his answer): this would works better?

$pgConnection = pg_connect('host=127.0.0.1 dbname=test user=myuser password=xxxxx')or die('cant connect');

pg_query("BEGIN");

$insert = pg_query("INSERT INTO customer (name) VALUES ('blabla')");

$last_id_query = pg_query("SELECT currval('customer_id_seq')");

$last_id_results = pg_fetch_assoc($last_id_query);

print_r($last_id_results);

//do somethings with the new customer id

pg_query("COMMIT");

pg_close($pgConnection);

Solution

  • If you use a newer version of PostgreSQL (> 8.1) you should use the RETURNING clause of INSERT (and UPDATE) command.

    OTOH if you insist on using one of the sequence manipulation functions, please read the fine manual. A pointer: "Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did."