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