I'm planning to use a "sequence" object to allow multiple clients of a
shared database to label table records with guaranteed-increasing serial
numbers. (The underlying problem is to let all the clients efficiently
find changes that any one of them makes. Every insertion or update will
assign a new sequence number to each new or modified record; then the
writer must issue a NOTIFY. Upon being notified, each client can read
all the newly-modified records with
SELECT ... FROM table WHERE seqno > lastseqno;
lastseqno := max(seqno seen in retrieved records);
where each client maintains a local variable lastseqno that's initially
zero. This should be fast if I provide an index on the seqno field.
BTW, does anyone know a better solution to this problem?)
What I noticed is that there's no good way to find out the current
sequence number value. The "currval" operator is no help because it
only tells you the last sequence number assigned in this client process
(and in fact it fails completely if used in a client that never executes
nextval because it is only a reader not a writer). The only way I can
see to do it reliably is to call nextval, thereby creating a gap in the
sequence (not a problem for my application) and wasting a sequence value
(definitely a problem if this is done a lot, since the scheme will fail
if the sequence object wraps around).
I think sequences ought to provide a "real" currval that reads the
current state of the sequence object from the database, thereby
returning the globally latest-assigned sequence value without depending
on any local state. (In the presence of caching this would produce the
latest value reserved by any backend, one which might not yet have been
used by that backend. But you can't use caching anyway if you depend on
the values to be assigned sequentially on a global basis.)
So far I haven't found any case where my application actually *needs* to
know the highest sequence number, so I'm not motivated to fix it (yet).
But I think this ought to be on the TODO list.
regards, tom lane