Skip site navigation (1) Skip section navigation (2)

FK's to refer to rows in inheritance child

From: Yeb Havinga <yebhavinga(at)gmail(dot)com>
To: PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: "w(dot)p(dot)dijkstra(at)mgrid(dot)net" <w(dot)p(dot)dijkstra(at)mgrid(dot)net>
Subject: FK's to refer to rows in inheritance child
Date: 2010-12-01 14:07:23
Message-ID: 4CF6569B.8070403@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello list,

FK's cannot refer to rows in inheritance childs. With some changes in 
LockRows, together with removing the ONLY keyword in ri_trigger.c, it 
was possible to refer to the rows in child relations. (WIP patch attached)

Though it passes simple tests, it is far from complete. To our knowledge 
the at minimal the following is missing:

1) child relations must be in sync with their parents regarding PK's as 
well. What 'in sync' means is probably also open for debate, i.e. what 
to propagate or block on ALTER TABLE and how to deal with multiple 
inheritance with no unique root parent.
2) uniqueness must be guaranteed not for each individual physical 
relation, but for the key in the whole inheritance tree. (how to name 
this? global uniqueness?)
3) support for CASCADE actions - currently actions on referenced rows in 
child relations are not cascaded.

We're currently figuring if there is consensus on the design approach 
for this topic. The wiki mentions these issues 
(http://wiki.postgresql.org/wiki/Todo#Inheritance) but must issues 
raised seem not to apply anymore.

Issue 1) will probably be straightforward to implement, once there is 
consensus how to implement the invariant 'if X, Y are relations and Y is 
child of X, then X.x is PK of X iff Y.x is PK of Y' (and there is 
consensus that that invariant should hold)

Issue 2) might be less trivial. On our radar is currently 
ExecInsertIndexTuples(). When inserting into a relation in an 
inheritance chain, also query the other (or all) relations in the chain 
for the inserted value, and throw an error when there is a non-empty 
result. Instead of constructing an inheritance tree manually in C, it 
might be an idea to use SPI in the style of the referential integrity 
checks, where the query would be a SELECT on the parent root(s) without 
the ONLY keyword, leaving figuring out which relations(indexes) to query 
to the inheritance planner.

Any thoughts or ideas are welcomed.

regards,
Yeb Havinga
Willem Dijkstra

Attached is the WIP patch that allows

BEGIN;
CREATE TABLE parent (a int PRIMARY KEY);
CREATE TABLE child (a int PRIMARY KEY) INHERITS (parent);
CREATE TABLE fk (a int REFERENCES parent(a));
END;

INSERT INTO child VALUES (10);
INSERT INTO fk VALUES (10);     -- must succeed
INSERT INTO fk VALUES (11);     -- should fail


Attachment: fk_inheritance.v1.patch
Description: text/x-patch (6.1 KB)

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-12-01 14:20:32
Subject: Re: profiling connection overhead
Previous:From: Heikki LinnakangasDate: 2010-12-01 14:05:34
Subject: Re: directory archive format for pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group