> 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.
Currently the parent and child share the same sequence.
ryan=> CREATE TABLE parent (i SERIAL);
NOTICE: CREATE TABLE will create implicit sequence parent_i_seq for SERIAL column parent.i
NOTICE: CREATE TABLE/UNIQUE will create implicit index parent_i_key for table parent
CREATE
ryan=> CREATE TABLE child (t text) INHERITS (parent);
CREATE
ryan=> INSERT INTO parent VALUES (NEXTVAL('parent_i_seq'));
INSERT 18731 1
ryan=> INSERT INTO child (t) values ('test');
INSERT 18732 1
ryan=> INSERT INTO parent VALUES (NEXTVAL('parent_i_seq'));
INSERT 18733 1
ryan=> SELECT * FROM parent;
i
-
1
3
(2 rows)
ryan=> SELECT * FROM child;
i|t
-+----
2|test
(1 row)
ryan=>
> 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
I'm not sure about the pg_dump, but I do not see the need to added complexity to the system table because of the shared
sequence. The
parent table can not be dropped while the child table exists, which would be the last reference to the
serial-sequence.
ryan=> drop table parent;
ERROR: Relation '18718' inherits 'parent'
ryan=>
This is the behavior I would expect, but then again I'm pretty new to databases ... and know nothing about the standard
:)
-Ryan
P.S. I hope to finish the patch tonight for the system table, but I will probably need some help/input on the pg_dump
issues.