FUNCTION bind (TABLE, COLUMN, SEQUENCE) returns OLD_SEQUENCE? (Was: Re: [HACKERS] Sequences....)

"D'Arcy J.M. Cain" wrote:
> Thus spake Tom Lane
> > "D'Arcy" "J.M." Cain <darcy(at)druid(dot)net> writes:
> > > Alternatively, maybe we can enforce the serialism of the type. Even
> > > if the user specifies a value, ignore it and put the next number in
> > > anyway.
> > I don't like that at *all*.
> I'm not entirely crazy about it myself. I included it as an option because
> it seemed to follow from the definition of serial number. However, in
> practice I imagine that people would find it overly restrictive.
>

Well, I'd do it a little different. If a sequence is bound
to a column, and the user provides a value, throw an error!

This is what I did in Oracle when I implemented system
assigned keys in a large project that I worked on. For
normal operations, this is the way you want it. Any other
way will be a nightmare! (I added the trigger to find the
client application that was being .. let's say .. very bad)

Now... for table loading, you have a different issue:

"D'Arcy J.M. Cain" wrote:
> > > Do as above but allow the user to specify a number as long as it is
> > > available and is lower than the next number in the series.
> > I think better would be that the sequence value is silently forced to
> > be at least as large as the inserted number, whenever a specific number
> > is inserted into a SERIAL field. That would ensure we never generate
> > duplicates, but not require keeping any extra state.
>
> I see your point but that could cause problems if you start your sequence
> too high. I guess the answer to that is, "Don't do that."
>
> Hmm. Are you suggesting that if I insert a number higher than the next
> sequence that the intervening numbers are never available?

If you are loading a table with records that are out of sequence,
then there is a manual issue involved.

Perhaps what is needed is a "bind" function:

FUNCTION bind( TABLE, COLUMN, SEQUENCE ) RETURNS OLD_SEQUENCE;

This procedure binds a table, column to auto-populate
with a given sequence. It returns the old sequence
(possibly null) associated with the TABLE/COLUMN.
The column, of course, must be an INT4 'compatible' type,
and the SEQUENCE cannot be bound to any other TABLE/COLUMN,
Also, the max(COLUMN) > curval(SEQUENCE)
If any of the conditions are false, then the BIND throws
an error, i.e., don't force the BIND to work.
Bind, of course, could use atttypmod field in pg_attributes.

If a sequence is associated with the TABLE/COLUMN during
dump, then DUMP will automatically treat them together
as a single unit. If the column appears in an INSERT
or an UPDATE, and the bound sequence is not null, then
an error is issued. Likewise, if nextval('sequence') is
called on a bound sequence, then an error is issued.

unbind(TABLE,COLUMN) is short for bind(TABLE,COLUMN,NULL);
"CREATE TABLE x ( y SERIAL );"
becomes short for
"CREATE TABLE x ( y INT4 ); CREATE SEQUENCE xys; BIND(x,y,xys);"

This gives you the best of both worlds. If you want to treat
the sequence, and table/column seperately, unbind them. Otherwise,
you may bind them together. So, if you are going to manually
mess with the column, then you must UNBIND the sequence,
do your altercations, and then REBIND the sequence back
to the table.

Thoughts?

Clark