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@druid.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-populatewith 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
bebound to any other TABLE/COLUMN,Also, the max(COLUMN) > curval(SEQUENCE) If any of the conditions are false, then the
BINDthrowsan 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 duringdump, then DUMP will automatically treat them together as a
singleunit.  If the column appears in an INSERTor an UPDATE, and the bound sequence is not null, thenan error is
issued. Likewise, if nextval('sequence') iscalled 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