Sequence objects have no global currval operator?
Sequence objects have no global currval operator?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
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
Re: [HACKERS] Sequence objects have no global currval operator?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Bruce Momjian 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
Re: [HACKERS] Sequence objects have no global currval operator?
От:
Bruce Momjian <maillist@candle.pha.pa.us>
Дата:
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)
