Thread: Re: [HACKERS] Sequences....

> Ryan Bradetich wrote:
> > > Back to my origional point .. I think we need another system table to map 
the
> > > sequence oid to the table's oid.  I've noticed this done with the 
inheritance,
> > > indexes, etc ... but I don't see a pg_sequence table.
> 
> Sounds good.
> 
> As long as a sequence can point to more than 
> one table/column combination.
> 
> Or, I guess, you can have the relationship 1-1 for
> the SERIAL type, but this should not prevent using
> sequences across more than one table if you don't use SERIAL.
> I often use a sequence for 3 or more tables in a system
> so that I can use 'generic' functions on the tables
> and produce reports without conficting primary keys..
> 
> :) Clark

Hmm.. Good points.... I'll make sure that doesn't happen.  Thanks for the tips. 
:)

-Ryan

>> As long as a sequence can point to more than 
>> one table/column combination.

Doesn't seem like a problem to me --- as far as I understood Ryan,
the new table he's proposing would only contain entries for sequences
created to implement SERIAL keywords.  For those, I think there should
indeed be a 1-1 mapping between parent table (+column) and resulting
sequence.

But yeah, don't break usage of ordinary standalone sequences ;-).

Another thing to think about is what it's going to take to dump and
reload this structure in pg_dump.  We need to be able to reconstitute
the system tables' contents and the current value of the SERIAL sequence
after a reload.
        regards, tom lane

BTW, has anyone thought twice about the interaction of SERIAL columns
with inheritance?  If I create a table having a SERIAL column and then
create a child table that inherits from the first, what happens?  Does
the child share the use of the parent's sequence (implying that serial
number assignments are unique across the parent and all its children)?
Or does the child get a new sequence object of its very own --- and if
so, what does that sequence object start out at?

We ought to find out what the current code actually does and then think
about whether we like it or not; I'll bet that the current behavior was
not designed but just fell out of the implementation.

If we do want shared use of a parent's sequence, that's going to
complicate Ryan's new system table considerably --- probably it needs
to have a row for each table using a particular sequence-created-to-
implement-SERIAL, and the sequence object can be deleted only when the
last reference to it goes away.  Life may become even more interesting
for pg_dump, too.
        regards, tom lane