Sequence objects have no global currval operator?

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

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.

What do you think of making currval return exactly this, only in the
case where nextval was not yet called by this client ?

I don't think anybody does rely on currval returning null iff nextval was not yet called
in his current session.

Andreas

Andreas Zeugswetter <andreas.zeugswetter@telecom.at> writes:

What do you think of making currval return exactly this, only in the
case where nextval was not yet called by this client ?

I don't think that would be helpful. If what you want is last_value
then the *only* safe way to get it is to use SELECT last_value.
Using currval in the way you suggest would be asking for trouble ---
your code will work until you add a nextval somewhere in the same
client, and then it will fail. Subtly.

As defined, currval is only useful for specialized uses, such as
assigning the same newly-allocated sequence number to multiple
fields or table rows. For example you could do
INSERT INTO table1 VALUES(nextval('seq'), ....);
INSERT INTO table2 VALUES(currval('seq'), ....);
INSERT INTO table3 VALUES(currval('seq'), ....);
This is perfectly correct and safe: all three tables will get the same
uniquely-allocated sequence number regardless of what any other clients
may be doing. You could also read back the assigned value with
SELECT nextval('seq');
and then insert the value literally into subsequent commands, but
that way requires an extra round trip to the server.

currval is not useful for inquiring about what other clients are doing,
and I think we are best off to leave it that way to avoid confusion.
I was only complaining because I didn't understand about last_value
at the start of this thread.

regards, tom lane