Re: ORDER BY random() LIMIT 1 slowness

On Wed, 18 Dec 2002, Jean-Luc Lachance wrote:

> OK Gabor,
>
> I'm the one who misunderstood.
>
> To me, it seem to be a bug (or at least a mis-feature) that one cannot
> call currval() before calling nextval().
>
> Does anyone know why it should be like this?

First, read this page:

http://developer.postgresql.org/docs/postgres/functions-sequence.html

which answers a bit of that question.

the real issue with sequences is that in order to be transactionally safe,
they have to live outside of all transactions.

The purpose of the sequence manipulation functions is to interact with
sequence's in ways that ensure that the same sequence number is never used
by two different transactions. Let me illustrate with a pair of
concurrent transactions, A and B:

A: begin;
B: begin;
A: select currval('seq'); <- client stores this value
B: select currval('seq'); <- ditto
A: insert into table (name, id) values ('john',idnum);
B: insert into table (name, id) values ('sue',idnum);
A: commit;
B: commit;

See the problem with the above? It's why you can't use currval to get the
sequence number if you haven't called nextval, setval, or some other
fuction that has changed the sequence, and why using it will cause an
error. Let's fix the above queries:

(seq=20)

A: begin;
B: begin;
A: select nextval('seq'); <- client doesn't store this (but could)
B: select nextval('seq'); <- client stores 22
A: insert into table (name, id) values ('john',currval('seq'));
B: insert into table (name, id) values ('sue',idnum);
A: commit;
B: commit;

All is well.

Note that if A: were to roll back, B would still complete, but we would
have a hole in our sequence for number 21. this is normal. The price we
pay for having sequences be safe in transactions is that they live outside
of transactions, and the functions that provide the interface are what are
transactionally aware.