public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Fetter <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: Joe Conway <[email protected]>
Cc: elein <[email protected]>
Cc: [email protected]
Subject: Re: [HACKERS] Tutorial
Date: Fri, 23 Jul 2004 11:51:46 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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;


Attachments:

  [text/plain] wtf.sql (950B, 2-wtf.sql)
  download | inline:
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;

view thread (19+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [HACKERS] Tutorial
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox