Обсуждение: Sequence objects have no global currval operator?

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

How about SELECT * FROM sequence_table_name?  Sequence numbers are
stored in their own tables.

> 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);


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> How about SELECT * FROM sequence_table_name?

Ah, of course.  The man page for CREATE SEQUENCE only mentions getting
the sequence parameters that way, but you can get the last_value as
well, which is exactly what I need.

Maybe I'll submit a documentation change to make this clearer for the
next guy.

            regards, tom lane