On Fri, Jul 23, 2004 at 09:03:30AM +0200, Peter Eisentraut wrote:
> Tom Lane wrote:
> > Robert Treat <[EMAIL PROTECTED]> writes:
> > > +1/2 (Since I don't like inheritence)
> > >
> > > IMHO we ought to try to keep the _tutorial_ free of things that
> > > are generally considered against relational design.
> >
> > Where is it written that inheritance is against relational design?
>
> I would venture that it is nowhere written that it is part of
> relational design. It is, however, unambiguously part of
> object-relational design, if that's what we're aiming for.
I see I have put my foot in it again. Please bear with me here.
Object-relational in general is not broken and is being worked on.
Custom data-types, custom aggregates, etc., etc. are working just
great, and lots of people use them.
What *is* broken is table inheritance, and the docs need to reflect
this.
If the parent table has a foreign key to another table foo, CASCADEing
DELETEs on foo leave ghost entries in the tables with inheritance.
Please find enclosed a repro, which demonstrates the problem on CVS
tip and 7.4.3.
Just an FYI, I first discovered this problem in a payment system.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
BEGIN;
CREATE TABLE foo (
foo_id SERIAL PRIMARY KEY
);
CREATE TABLE parent (
parent_id SERIAL PRIMARY KEY
, foo_id INTEGER NOT NULL REFERENCES foo(foo_id) ON DELETE CASCADE
, parent_1_text TEXT NOT NULL
);
CREATE TABLE child_1 (
child_1_text TEXT NOT NULL
) INHERITS(parent);
CREATE TABLE child_2 (
child_2_text TEXT NOT NULL
) INHERITS(parent);
INSERT INTO foo VALUES(DEFAULT);
INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)
VALUES (currval('public.foo_foo_id_seq'), 'parent text 1', 'child_1 text 1');
INSERT INTO foo VALUES(DEFAULT);
INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)
VALUES (currval('public.foo_foo_id_seq'), 'parent text 2', 'child_1 text 2');
INSERT INTO foo VALUES(DEFAULT);
INSERT INTO child_2 (foo_id, parent_1_text, child_2_text)
VALUES (currval('foo_foo_id_seq'), 'parent text 3', 'child_2 text 1');
DELETE FROM foo WHERE foo_id = 1;
SELECT * FROM parent;
SELECT * FROM child_1;
ROLLBACK;
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
