pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIntegrity and Inheritance
4+ messages / 3 participants
[nested] [flat]
* Integrity and Inheritance
@ 2001-06-10 05:08 Christophe Labouisse <labouiss@cybercable.fr>
2001-06-10 05:23 ` Re: Integrity and Inheritance Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Christophe Labouisse @ 2001-06-10 05:08 UTC (permalink / raw)
To: pgsql-sql
I wanted to build the following schema :
- one "generic" document table with a column doc_id ;
- a couple of "specific" document tables inheriting from doc ;
- a table refering a document by it's id with and integrity constraint
on it.
In SQL :
CREATE TABLE doc (
doc_id serial PRIMARY KEY,
);
CREATE TABLE lexique (
) INHERITS (doc);
CREATE TABLE word_doc (
id serial PRIMARY KEY,
doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE,
);
What I tried to do next is to insert a new "lexique" entry, and then a
bunch of "word_doc" rows refering this entry. The last part fails with
a constraint violation this is quite normal since the trigger in
backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY".
What should I do ? Should I consider another way to do what I want,
rewrite a trigger to replace the system one ?
--
Le cinéma en Lumière : http://www.lumiere.org/
Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Integrity and Inheritance
2001-06-10 05:08 Integrity and Inheritance Christophe Labouisse <labouiss@cybercable.fr>
@ 2001-06-10 05:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-10 16:11 ` Re: Integrity and Inheritance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2001-06-10 05:23 UTC (permalink / raw)
To: Christophe Labouisse <labouiss@cybercable.fr>; +Cc: pgsql-sql
On 10 Jun 2001, Christophe Labouisse wrote:
> I wanted to build the following schema :
>
> - one "generic" document table with a column doc_id ;
> - a couple of "specific" document tables inheriting from doc ;
> - a table refering a document by it's id with and integrity constraint
> on it.
>
> In SQL :
>
> CREATE TABLE doc (
> doc_id serial PRIMARY KEY,
> );
>
> CREATE TABLE lexique (
> ) INHERITS (doc);
>
> CREATE TABLE word_doc (
> id serial PRIMARY KEY,
> doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE,
> );
>
>
> What I tried to do next is to insert a new "lexique" entry, and then a
> bunch of "word_doc" rows refering this entry. The last part fails with
> a constraint violation this is quite normal since the trigger in
> backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY".
>
> What should I do ? Should I consider another way to do what I want,
> rewrite a trigger to replace the system one ?
One problem is that for update isn't supported across inheritance trees
AFAICS and the triggers use for update for the appropriate locking. If
you were to write a trigger that worked across the tree, you'd probably
need to take that into account for the locking.
You might be best off not using inheritance, and giving the "subtables"
ids that refer back to a separate doc_id table that keeps the ids. This
has the downside of requiring a separate write for the doc_id and document
tables, but means that you don't hit any of the wierdness surrounding
inheritance.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Integrity and Inheritance
2001-06-10 05:08 Integrity and Inheritance Christophe Labouisse <labouiss@cybercable.fr>
2001-06-10 05:23 ` Re: Integrity and Inheritance Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-06-10 16:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-10 21:03 ` Re: Integrity and Inheritance Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2001-06-10 16:11 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Christophe Labouisse <labouiss@cybercable.fr>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> One problem is that for update isn't supported across inheritance trees
> AFAICS and the triggers use for update for the appropriate locking.
It's possible (maybe even likely) that this could be fixed with minimal
work. I punted on it for 7.1 due to lack of time. However, is that
really the major obstacle in the way of inherited foreign keys? Seems
like there are a bunch of issues that'd require cross-table unique
indexes, as well.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Integrity and Inheritance
2001-06-10 05:08 Integrity and Inheritance Christophe Labouisse <labouiss@cybercable.fr>
2001-06-10 05:23 ` Re: Integrity and Inheritance Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-10 16:11 ` Re: Integrity and Inheritance Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-10 21:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-06-10 21:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christophe Labouisse <labouiss@cybercable.fr>; pgsql-sql
On Sun, 10 Jun 2001, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > One problem is that for update isn't supported across inheritance trees
> > AFAICS and the triggers use for update for the appropriate locking.
>
> It's possible (maybe even likely) that this could be fixed with minimal
> work. I punted on it for 7.1 due to lack of time. However, is that
> really the major obstacle in the way of inherited foreign keys? Seems
> like there are a bunch of issues that'd require cross-table unique
> indexes, as well.
No, it's not the major constraint. But he wanted to know if he should
make his own triggers (presumably with ours as a starting point). The
first major problem he'd see is that as soon as he took the ONLY off
the constraint would suddenly fail because of the for update. In the
past (before noticing the comment on for update and inheritance), I'd
suggested to people that they might try making a trigger that way. The
uniqueness thing really plays into the ref actions more than the
check, and he'd possibly have to write those from scratch anyway.
And I think there are some places where we grab a lock on the pk
table which might need to be extended to the children. Actually, the
uniqueness thing may also become less problematic once I finish making the
constraints check to see if another matching row has been inserted in
the time between the creation of the trigger and its running.
Some of what's necessary depends on things that we might want to do to
inherited tables (column renaming for example and which way unique
should work), so I'm not all that motivated to look at it until a concrete
plan was put down for how inheritance is going to change. I wouldn't
mind doing the legwork on the FK constraints at that point.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-06-10 21:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-10 05:08 Integrity and Inheritance Christophe Labouisse <labouiss@cybercable.fr>
2001-06-10 05:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-10 16:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-10 21:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox