DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=gOVx3GWdgWnT79uO4nOxNSG8jBtxbZUIcPUTetR3AchUj0RQLqmL9PlOS7znT3FIGaFXWMo+e2cf/nHIM5X9KrsStSggJeShQjznqegcUquSYA7Eoy+CDc50DFJj5+0+TIHn1Sg15EQUhFIpekb/ANKojFRnxCnTCiWzRLSpTG4=
Message-ID: <a10a823c05070713473add03d7@mail.gmail.com>
Date: Thu, 7 Jul 2005 14:47:23 -0600
From: Larry Meadors <larry.meadors@gmail.com>
Reply-To: lmeadors@apache.org
To: Michael Fuhr <mike@fuhr.org>
Subject: Re: getting back autonumber just inserted
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20050707203715.GA26528@winnie.fuhr.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <42CD6B58.6060508@techevolution.be>
	<20050707201433.94901.qmail@web41013.mail.yahoo.com>
	<20050707203715.GA26528@winnie.fuhr.org>

If you have a trigger on your table that inserts a record in a table
and shares the same sequence, what value do you get back, the
triggered curval, or the currently inserted one?

Being a lazy bum, this is why I still prefer the "get key - insert
record" approach. Less brain power required. ;-)

Larry

On 7/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote:
> >
> > you have to use currval inside a transaction...
>=20
> Not true.  Have you observed otherwise?
>=20
> > 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.
>=20
> 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:
>=20
> http://www.postgresql.org/docs/8.0/static/functions-sequence.html
>=20
> "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."
>=20
> http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3
>=20
> "4.11.3) Doesn't currval() lead to a race condition with other users?
>=20
> "No.  currval() returns the current value assigned by your session, not b=
y
> all sessions."
>=20
> 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.
>=20
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>