Date: Thu, 7 Jul 2005 14:37:15 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Theodore Petrosky <tedpet5@yahoo.com>
Cc: mail TechEvolution <mail@techevolution.be>,
	pgsql-sql@postgresql.org
Subject: Re: getting back autonumber just inserted
Message-ID: <20050707203715.GA26528@winnie.fuhr.org>
References: <42CD6B58.6060508@techevolution.be>
	<20050707201433.94901.qmail@web41013.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050707201433.94901.qmail@web41013.mail.yahoo.com>

On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote:
> 
> you have to use currval inside a transaction...

Not true.  Have you observed otherwise?

> begin;
> insert something that increments the counter;
> select currval('sequence_name');
> end;
> 
> using currval inside a transaction guarantees that the
> value is correct for your insert statement and has not
> changed by another insert statement.

currval() returns the most recently obtained value from the sequence
in the current session, regardless of what other sessions are doing
or whether the current session is in a transaction.  See the
documentation and the FAQ:

http://www.postgresql.org/docs/8.0/static/functions-sequence.html

"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."

http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3

"4.11.3) Doesn't currval() lead to a race condition with other users?

"No.  currval() returns the current value assigned by your session, not by
all sessions."

You can do experiments to confirm what the documentation states.
If you see different behavior then please put together a self-contained
test case and report it as a bug.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/