Calling currval within a MDB2/Postgresql transaction

polymnia

What is the best way to handle transactions if I need to output a sequence currval from an insert?

Im wondering if calling the following SQL through MDB2 will ensure that the currval result is the product of the INSERTS. Im just a little foggy with how MDB2 and PostgreSQL are working here- I've seen documentation on using the MDB2 methods such as commit etc... but I'd rather do it in SQL if possible.

This code works, I'm just wondering if it will work correctly with a bunch of concurrent connections.

$mdb2->query(
'BEGIN TRANSACTION;
INSERT INTO sales VALUES (nextval(\'sales_sales_id_seq\'));
INSERT INTO transaction (sales_id) SELECT currval(\'sales_sales_id_seq\');');

$transaction_id_res =$mdb2->query('SELECT currval(\'transaction_transaction_id_seq\') AS transaction_id;');

$mdb2>query('COMMIT TRANSACTION;');
$transaction_id_row = $transaction_id_res->fetchRow();
$transaction_id = $transaction_id_row['transaction_id'];

pete_bisby

Personally, I would create a stored procedure within PostgreSQL and then call this from PHP; the stored procedure would perform your INSERTs then return the currval of the sequence you are using.

Sxooter

polymnia wrote:
What is the best way to handle transactions if I need to output a sequence currval from an insert?

Im wondering if calling the following SQL through MDB2 will ensure that the currval result is the product of the INSERTS. Im just a little foggy with how MDB2 and PostgreSQL are working here- I've seen documentation on using the MDB2 methods such as commit etc... but I'd rather do it in SQL if possible.

If you're running pgsql 8.2 you can use the returning clause. Example:

mydb=# create table test (id serial primary key, info text);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
mydb=# insert into test (info) values ('this is a string') returning id;
 id
----
  1
(1 row)

INSERT 0 1

Sxooter

p.s. a followup on currval / nextval

the way they work is that within a given session, you can only call currval after nextval has been called. and the value that currval returns is correct for your session and your session alone. It's transaction safe / transaction-proof

polymnia

Sxooter and Pete- thanks for the help. The stored procedure/function idea is great... I have them running elsewhere in this app, and Im not sure why I didn't think to do that here. The returning clause is great info... I have a few work arounds that I can avoid now with this.

Sxooter, what exactly constitutes a session? Is a db session started and ended every time a block of SQL is sent over to Postgres via MDB2?