Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 31378475A61 for ; Wed, 6 Mar 2002 14:23:37 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 61E0CD605; Wed, 6 Mar 2002 11:23:34 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 575995BFD; Wed, 6 Mar 2002 11:23:34 -0800 (PST) Date: Wed, 6 Mar 2002 11:23:34 -0800 (PST) From: Stephan Szabo To: Frank Joerdens Cc: Subject: Re: SELECT currval within a transaction In-Reply-To: <20020306180157.B25140@superfly.archi-me-des.de> Message-ID: <20020306112228.R9719-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200203/68 X-Sequence-Number: 6950 On Wed, 6 Mar 2002, Frank Joerdens wrote: > When inserting stuff I regularly come across the problem of wanting the > value of the primary key row of the newly inserted row. In order to get > it, I put the INSERT statement together with a SELECT currval into a > transaction, like > > BEGIN WORK; INSERT INTO foo (bar) VALUES ('bla'); SELECT > currval('foo_id_seq'); COMMIT WORK; > > My question is: Does this always work? My assumption obviously is that > it does, i.e. that currval always returns the current value of the > sequence as seen from within the transaction. It is conceivable > (although I don't think it would make much sense) though that currval > would return a different value if some other client did an insert right > after the INSERT in the transaction (and currval would return a higher > value). I don't believe it could be implemented that way because IMHO > it'd break the whole notion of the atomicity of a transaction. But I'd > like to be sure. currval is defined to always return the value last given to your session, so the only case you could have that fail that I see would be if a rule or trigger invoked nextval on the sequence after the value was gotten for the insert.