Re: [INTERFACES] locking on database updates

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(at)wallace(dot)ece(dot)rice(dot)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