Currval and transactions.

Hello,

Ok, so i'm trying to insert a row and return the id of that row as supplied by the DEFAULT directive.

And i'm stuck on v8.1.x or i'd use the "RETURNING" construct.

And i've already found a message on this list that suggests a structure such as:

BEGIN TRANSACTION;
INSERT INTO $tableName
( instance_name, instance_id )
VALUES
( '$instanceName', DEFAULT );
SELECT CURRVAL('".$tableName."_instance_id_seq');
COMMIT TRANSACTION;

however i've found that this structure doesnt return anything, it completes successfully but i get no results, when i run it through pgadmin i get the message:
Query result with 1 rows discarded.

Query returned successfully with no result in 2 ms.

if i drop the transaction declarations at the top and bottom i get the desired result, the id of the most recently created row, but i want to make sure that by dropping those declarations, i'm not going to be subject to a race condition. Basically i'm asking if the two queries, when sent together, will be executed in a single transaction, or in two separate transactions which could be subject to said race condition.

Thanks in advance for your help.

Adam Gentz
Web Developer
BizXchange
1100 Olive Way, Suite 1720
Seattle, WA 98101

Ph: (206) 442-4706
Fax: (206) 447-9966
www.bizx.com

BizXchange is honored to be recognized as one of the
2009 PSBJ 100 Fastest-Growing Private Companies in Washington