pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT currval within a transaction
2+ messages / 2 participants
[nested] [flat]
* SELECT currval within a transaction
@ 2002-03-06 17:01 Frank Joerdens <frank@joerdens.de>
2002-03-06 19:23 ` Re: SELECT currval within a transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Frank Joerdens @ 2002-03-06 17:01 UTC (permalink / raw)
To: pgsql-sql
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.
Regards, Frank
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SELECT currval within a transaction
2002-03-06 17:01 SELECT currval within a transaction Frank Joerdens <frank@joerdens.de>
@ 2002-03-06 19:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2002-03-06 19:23 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-03-06 19:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-06 17:01 SELECT currval within a transaction Frank Joerdens <frank@joerdens.de>
2002-03-06 19:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox