Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu
 [128.42.12.154])
	by hub.org (8.9.3/8.9.3) with ESMTP id OAA26284
	for <pgsql-interfaces@postgresql.org>;
	Tue, 7 Dec 1999 14:24:15 -0500 (EST)
	(envelope-from reedstrm@wallace.ece.rice.edu)
Received: by wallace.ece.rice.edu via sendmail from stdin
	id <m11vQDb-000LEEC@wallace.ece.rice.edu> (Debian Smail3.2.0.102)
	for pgsql-interfaces@postgresql.org;
	Tue, 7 Dec 1999 13:24:11 -0600 (CST)
Date: Tue, 7 Dec 1999 13:24:11 -0600
From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
To: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] locking on database updates
Message-ID: <19991207132411.B7090@rice.edu>
References: <19991207111234.A6021@rice.edu> <23996.944589573@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <23996.944589573@sss.pgh.pa.us>;
	from tgl@sss.pgh.pa.us on Tue, Dec 07, 1999 at 12:59:33PM -0500

Tom - 
I'm surprised: that one's been beat to death in earlier incarnations
of this FAQ. The currval() function is part of backend state: it
always returns the last value sent to _this connection_. In fact, it's
undefined (and throws an error) in a connection until a nextval() has
been performed. Who ever implemented currval did it right.

============session 1=====================

idas_demo=> create table foo (bar serial, baz text);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_bar_seq' for SERIAL column 'foo.bar'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'foo_bar_key' for table 'foo'
CREATE
idas_demo=> insert into foo (baz) values ('wooble');
INSERT 692575 1
idas_demo=> insert into foo (baz) values ('wibble');
INSERT 692576 1
idas_demo=> select currval('foo_bar_seq');
currval
-------
      2
(1 row)

============session 2=====================

idas_demo=> select currval('foo_bar_seq');
ERROR:  foo_bar_seq.currval is not yet defined in this session
idas_demo=>  insert into foo (baz) values ('wibble');
INSERT 692608 1
idas_demo=>  insert into foo (baz) values ('wibble');
INSERT 692609 1
idas_demo=> select currval('foo_bar_seq');
currval
-------
      4
(1 row)

idas_demo=> 

============session 1=====================

idas_demo=> select currval('foo_bar_seq');
currval
-------
      2
(1 row)

idas_demo=> 


I'm not sure it's even slower: since currval is local to the backend,
it may be that it doesn't touch the sequence per se: I'd have to check
the code.

Of the sequence functions, one that's not multi-user safe, as far as
I can tell, is setval(). I think that sets the master sequence counter
for all clients.

Ross

On Tue, Dec 07, 1999 at 12:59:33PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> > create table foo (bar serial, baz text);
> > insert into foo (baz) values ('wooble');
> > select currval('foo_bar_seq');
> 
> I don't think this is safe in a multi-client environment;
> what if someone else inserts at about the same time?
> 
> Better to do
> 	select nextval('foo_bar_seq');
> 	insert into foo values (just-retrieved-value, 'wooble');
> which is safer and probably marginally faster (since the
> sequence object is touched only once, not twice).
> 
> 			regards, tom lane