Обсуждение: Notes about int8 sequences
I have been thinking about implementing int8-based sequences to go along with the existing int4-based ones. The amount of code involved doesn't seem very large, but there are some interesting questions about the API. Some points for discussion: * On machines that don't offer an 8-byte-int C datatype, the int8 sequence type would still exist, but it couldn't actually count higher than 2^31. This is the same as the behavior of our int8 datatype on such machines. * What should be the CREATE syntax for such sequences? I lean towards adding an optional clause to CREATE SEQUENCE, which might be spelled like "TYPE INT8", "TYPE BIGINT", or just "INT8" or "BIGINT". * How should one invoke nextval() and friends on such a sequence? Directly applying the existing convention, eg, nextval('sequencename'), won't work because those functions are declared to return int4. One possible answer is to require people to write nextval8('sequencename') and so forth. This is ugly; it would be nice to allow automatic overloading of the function name the way we can do for most datatypes. We have had discussions to the effect that this method of referencing sequences is ugly and broken, anyway. Perhaps we could allow people to write nextval(sequencename) and/or sequencename.nextval, which would expose the sequence object to the parser so that datatype overloading could occur. I am envisioning having two archetype sequence objects, one int4 and the other int8, and making every other sequence object be an inheritance child of one of these. Then, declaring nextval functions that operate on the two parent datatypes would work --- at least to the extent that we could do type resolution to choose which function to apply. I'm not sure yet how to keep the parser from adding the sequence to the query's join set when you do something like that :-(. It would be easier to make it work for the sequencename.nextval notation, I think, but do we want to encourage people to use that syntax? It's a PostQuel-ism that we may have to discard in order to support SQL92 schemas. In any case, can anyone think of cases where it's a good idea to allow the sequence name to be specified as a string --- for example, because you want to compute the sequence name at runtime? To support that, I think we'd have little choice but to accept nextval8('sequencename'). I'd rather move away from the string-based approach, but I don't know if we can get away with that. regards, tom lane
Allan Engelhardt <allane@cybaea.com> writes: > I'm not really a hacker, but why couldn't you simply change nextval to > return int8 in all cases? Hmm. That's a possibility. There's some potential for trouble if an application is expecting an int4 result from "SELECT nextval()" and gets int8 instead, but if we think we could live with that... Actually, if we thought we could live with that, my inclination would be to blow off int4-based sequences altogether, and just redefine SEQUENCE objects as operating on INT8. Interesting thought, eh? regards, tom lane
Tom Lane wrote: > * How should one invoke nextval() and friends on such a sequence? > Directly applying the existing convention, eg, nextval('sequencename'), > won't work because those functions are declared to return int4. One I'm not really a hacker, but why couldn't you simply change nextval to return int8 in all cases? Presumably there is anautomatic (and silent) conversion from int8 to int4 where the range fits? The overhead of creating an int8 return valuefor an old-style int4 sequence (and converting it back to int4 for the INSERT/UPDATE) seems very small. I'm missing something obvious again? Allan.
On Mon, 6 Aug 2001, Tom Lane wrote: > Hmm. That's a possibility. There's some potential for trouble if an > application is expecting an int4 result from "SELECT nextval()" and > gets int8 instead, but if we think we could live with that... I assume there will be the same limitations as you mentioned in your original message. Ie. some systems don't have an 8-byte-int C datatype so would still have the 2^31 limit. > Actually, if we thought we could live with that, my inclination would be > to blow off int4-based sequences altogether, and just redefine SEQUENCE > objects as operating on INT8. Interesting thought, eh? More than interesting ... excellant. Bigger is better, right? Cheers, Rod -- Remove the word 'try' from your vocabulary ... Don't try. Do it or don't do it ... Steers try! Don Aslett
"Roderick A. Anderson" <raanders@tincan.org> writes: > On Mon, 6 Aug 2001, Tom Lane wrote: >> Hmm. That's a possibility. There's some potential for trouble if an >> application is expecting an int4 result from "SELECT nextval()" and >> gets int8 instead, but if we think we could live with that... > I assume there will be the same limitations as you mentioned in your > original message. Ie. some systems don't have an 8-byte-int C datatype > so would still have the 2^31 limit. Check. >> Actually, if we thought we could live with that, my inclination would be >> to blow off int4-based sequences altogether, and just redefine SEQUENCE >> objects as operating on INT8. Interesting thought, eh? > More than interesting ... excellant. Bigger is better, right? Until it breaks your app, yes ;-) One thing that would have to be thought about is whether the SERIAL pseudo-type should generate an int8 instead of int4 column. On compatibility grounds, it might be better to leave it generating int4, and invent a second pseudo-type SERIAL8 that is just the same except for making an int8 column. I'm more worried about changing the datatype of a user column than I am about changing the output type of nextval(), so I'd be sort of inclined to have two SERIAL types even if we change nextval() to int8. Thoughts? regards, tom lane
> One thing that would have to be thought about is whether the SERIAL > pseudo-type should generate an int8 instead of int4 column. On > compatibility grounds, it might be better to leave it generating int4, > and invent a second pseudo-type SERIAL8 that is just the same except > for making an int8 column. I'm more worried about changing the datatype > of a user column than I am about changing the output type of nextval(), > so I'd be sort of inclined to have two SERIAL types even if we change > nextval() to int8. Thoughts? Hmm. How far away are we from doing SERIAL in a way that you find more acceptable than the current technique of mucking around internally with sequences and default values? Changes there may not be impacted by decisions we make now on an int8 type, but it might be good to think about it beforehand. If we do blast ahead with a SERIAL8, then we should consider implementing a SERIAL4 and then aliasing SERIAL to one or the other (can be done in the parser as you know). - Thomas
Thomas Lockhart <lockhart@alumni.caltech.edu> writes: > Hmm. How far away are we from doing SERIAL in a way that you find more > acceptable than the current technique of mucking around internally with > sequences and default values? I'd say "won't happen for 7.2", whereas it seems like changing sequences to use int8 is something that could get done this month. A true SERIAL type is something that we should think about along with per-table OID generation, since they have essentially the same requirement: a lightweight sequence generator. Our present approach of a one-row table to represent a sequence is not sufficiently lightweight, IMHO, either from an implementation or a conceptual viewpoint. (In particular, it requires each sequence to have a unique name taken from the table namespace, whereas for both table OIDs and serial columns I think we'd much prefer the sequences to be anonymous ... or at least in a different namespace. But how do we change that without breaking a lot of existing application code?) Offhand I don't see that adding a SERIAL8 type to the mix (or just changing SERIAL to be int8) would make this any harder or easier. The underlying implementation is exposed just as much as before, but not any more so. > If we do blast ahead with a SERIAL8, then we should consider > implementing a SERIAL4 and then aliasing SERIAL to one or the other (can > be done in the parser as you know). Sure, that'd be a reasonable way to set it up, if we decide to have two SERIAL types. regards, tom lane
At 07:02 PM 06-08-2001 -0400, Tom Lane wrote: >pseudo-type should generate an int8 instead of int4 column. On >compatibility grounds, it might be better to leave it generating int4, >and invent a second pseudo-type SERIAL8 that is just the same except >for making an int8 column. I'm more worried about changing the datatype >of a user column than I am about changing the output type of nextval(), >so I'd be sort of inclined to have two SERIAL types even if we change >nextval() to int8. Thoughts? serial8 sounds ok to me. I use currval. Cheerio, Link.
On Mon, 6 Aug 2001, Tom Lane wrote: > * How should one invoke nextval() and friends on such a sequence? > Perhaps we could allow people to write nextval(sequencename) and/or > sequencename.nextval, which would expose the sequence object to the > parser so that datatype overloading could occur. I'm not worried about the size of the return type of a sequence, but I like the idea of Oracle-compatible "seq.nextval" syntax. Matthew.
Matthew Kirkwood <matthew@hairy.beasts.org> writes: > I'm not worried about the size of the return type of > a sequence, but I like the idea of Oracle-compatible > "seq.nextval" syntax. I didn't realize we had any Oracle-compatibility issues here. What exactly does Oracle's sequence facility look like? regards, tom lane
On Tue, 7 Aug 2001, Tom Lane wrote: > > I'm not worried about the size of the return type of > > a sequence, but I like the idea of Oracle-compatible > > "seq.nextval" syntax. > > I didn't realize we had any Oracle-compatibility issues here. What > exactly does Oracle's sequence facility look like? It's exactly "seqname.nextval". It seems that it can be used in exactly the places where PG allows nextval("seqname") (subject to the usual sprinkling of "from dual"s, of course). Matthew.