Обсуждение: Integrity and Inheritance
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 DELETECASCADE, ); 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
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.
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
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.