Thread: 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.