Date: Fri, 23 Jul 2004 11:51:46 -0700
From: David Fetter <david@fetter.org>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Treat <xzilla@users.sourceforge.net>,
	Joe Conway <mail@joeconway.com>, elein <elein@varlena.com>,
	pgsql-docs@postgresql.org
Subject: Re: [HACKERS] Tutorial
Message-ID: <20040723185146.GV7751@fetter.org>
References: <20040722222104.GU7751@fetter.org>
	<200407222240.45890.xzilla@users.sourceforge.net>
	<15070.1090552467@sss.pgh.pa.us>
	<200407230903.30389.peter_e@gmx.net>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="AhhlLboLdkugWU4S"
Content-Disposition: inline
In-Reply-To: <200407230903.30389.peter_e@gmx.net>
User-Agent: Mutt/1.4.1i

--AhhlLboLdkugWU4S
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, Jul 23, 2004 at 09:03:30AM +0200, Peter Eisentraut wrote:
> Tom Lane wrote:
> > Robert Treat <xzilla@users.sourceforge.net> 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 david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

--AhhlLboLdkugWU4S
Content-Type: text/plain; charset=us-ascii
Content-Description: Repro.sql
Content-Disposition: attachment; filename="wtf.sql"

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;

--AhhlLboLdkugWU4S--