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

> "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> > > Thus spake Ryan Bradetich
> > >> 2. Create a new data type serial.  I haven't thought idea out much,
> > >> and need to investigate it some more. I'm thinking it would be binary
> > >> equivilent with the int4 type, and use most of the existing seqence
> > >> code, but not put the seqence name entry in the pg_class system
> > >> table.  Does this sound like a feasible idea?
> > 
> > > I like it.
> > 
> > A binary-equivalent type does seem like a handier way to represent
> > SERIAL than what we are doing.  You still need a way to find the
> > associated sequence object, though, so a table mapping from
> > table-and-column to sequence OID is still necessary.  (Unless we
> > were to use the atttypmod field for the column to hold the sequence
> > object's OID?  Seems a tad fragile though, since there's no way to
> > update an atttypmod field in an existing table.)
> 
> atttypmod seems like a perfect idea.  We also need a unique type for
> large objects, so oid's and large objects can be distinguished.  We
> could do both at the same time, and with Thomas's new type coersion
> stuff, we don't need to create tons of functions for each new type.

I'll play around with this idea for a while and see what I come up with.  I'm 
not sure if I completely understand, but I'll form questions as I continue to 
dig into the source code. :)

> > 
> > I don't like the idea of not putting the sequence name into pg_class.
> > That would mean that the sequence object is not directly accessible
> > for housekeeping operations.  If you do that, you'd have to invent
> > all-new ways to do the following:
> >     * currval, setval, nextval (yes there are scenarios where a
> >       direct nextval on the sequence is useful)
> >     * dump and reload the sequence in pg_dump
> 
> Yes, let's keep it in pg_class.  No reason not to.

Ok, you convicned me.

> > > If we decide to leave things more or less as they are, how about a new
> > > flag for sequences and indexes that sets a row as system generated
> > > rather than user specified?  We can then set that field when a sequence
> > > or index is generated by the system such as for the serial type or
> > > primary keys.
> > 
> > Yes, it'd be nice to think about fixing up primary-key implicit indexes
> > while we are at it --- they have some of the same problems as SERIAL ...

I'm not following this... When a table is dropped, all the indexes for that 
table get dropped.  The indexes are associated with a table, whereas the 
sequences are just sequences not associated with a table.  Am I understanding 
the issue correctly?

> My guess is that 6.5 is too close to be making such sweeping changes,
> though the pg_dump problems with SERIAL certainly make this an important
> issue.

Do you want me to try and get the serial stuff finished before 6.5? or should we 
wait?

-Ryan

Ryan Bradetich <rbrad@hpb50023.boi.hp.com> writes:
>>>> Yes, it'd be nice to think about fixing up primary-key implicit indexes
>>>> while we are at it --- they have some of the same problems as SERIAL ...

> I'm not following this... When a table is dropped, all the indexes for
> that table get dropped.  The indexes are associated with a table,
> whereas the sequences are just sequences not associated with a table.
> Am I understanding the issue correctly?

It's mainly a pg_dump issue: can pg_dump identify such an index as
having come from a PRIMARY KEY spec rather than a separate CREATE INDEX
command?  This goes back to the complaint about pg_dump not being able
to fully reconstruct the logical connections in a database.

A related issue is inheritance: if I say PRIMARY KEY in the definition
of a table, and then make a child table that inherits from that table,
I'd expect the child's field to act like a PRIMARY KEY too --- in other
words it should have a unique index created for it.  Right now I don't
believe that that happens.

What it all comes down to is that mapping these structures into "lower
level" objects without remembering the higher-level structure isn't
fully satisfactory.  We need an explicit, persistent representation of
the PRIMARY KEY attribute.  In that way it's the same problem as SERIAL.
The best solutions might be different, however.
        regards, tom lane

> > > Yes, it'd be nice to think about fixing up primary-key implicit indexes
> > > while we are at it --- they have some of the same problems as SERIAL ...
> 
> I'm not following this... When a table is dropped, all the indexes for that 
> table get dropped.  The indexes are associated with a table, whereas the 
> sequences are just sequences not associated with a table.  Am I understanding 
> the issue correctly?

Not sure.  It just seem to relate.

> 
> > My guess is that 6.5 is too close to be making such sweeping changes,
> > though the pg_dump problems with SERIAL certainly make this an important
> > issue.
> 
> Do you want me to try and get the serial stuff finished before 6.5? or should we 
> wait?

Probably wait, unless we can do it easily.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Thus spake Ryan Bradetich
> > > > If we decide to leave things more or less as they are, how about a new
> > > > flag for sequences and indexes that sets a row as system generated
> > > > rather than user specified?  We can then set that field when a sequence
> > > > or index is generated by the system such as for the serial type or
> > > > primary keys.
> > > 
> > > Yes, it'd be nice to think about fixing up primary-key implicit indexes
> > > while we are at it --- they have some of the same problems as SERIAL ...
> 
> I'm not following this... When a table is dropped, all the indexes for that 
> table get dropped.  The indexes are associated with a table, whereas the 
> sequences are just sequences not associated with a table.  Am I understanding 
> the issue correctly?

I was thinking more for pg_dump.  If it is a system index, don't dump it.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.