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
diff --git a/src/backend/executor/nodeLockRows.c b/src/backend/executor/nodeLockRows.c
index 440a601..642fb4f 100644
--- a/src/backend/executor/nodeLockRows.c
+++ b/src/backend/executor/nodeLockRows.c
@@ -78,8 +78,8 @@ lnext:
if (node->lr_epqstate.estate != NULL)
EvalPlanQualSetTuple(&node->lr_epqstate, erm->rti, NULL);
- /* if child rel, must check whether it produced this row */
- if (erm->rti != erm->prti)
+ /* if inheritance rel, must check whether it produced this row */
+ if (erm->toidAttNo != InvalidAttrNumber)
{
Oid tableoid;
@@ -195,12 +195,9 @@ lnext:
HeapTupleData tuple;
Buffer buffer;
- /* ignore non-active child tables */
+ /* ignore non-active inheritance parent or child tables */
if (!ItemPointerIsValid(&(erm->curCtid)))
- {
- Assert(erm->rti != erm->prti); /* check it's child table */
continue;
- }
if (EvalPlanQualGetTuple(&node->lr_epqstate, erm->rti) != NULL)
continue; /* it was updated and fetched above */
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 33a8935..2a73e4a 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -338,12 +338,12 @@ RI_FKey_check(PG_FUNCTION_ARGS)
/* ---------
* The query string built is
- * SELECT 1 FROM ONLY <pktable>
+ * SELECT 1 FROM <pktable>
* ----------
*/
quoteRelationName(pkrelname, pk_rel);
snprintf(querystr, sizeof(querystr),
- "SELECT 1 FROM ONLY %s x FOR SHARE OF x",
+ "SELECT 1 FROM %s x FOR SHARE OF x",
pkrelname);
/* Prepare and save the plan */
@@ -463,14 +463,14 @@ RI_FKey_check(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...] FOR SHARE
+ * SELECT 1 FROM <pktable> WHERE pkatt1 = $1 [AND ...] FOR SHARE
* The type id's for the $ parameters are those of the
* corresponding FK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo.nkeys; i++)
{
@@ -625,14 +625,14 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...] FOR SHARE
+ * SELECT 1 FROM <pktable> WHERE pkatt1 = $1 [AND ...] FOR SHARE
* The type id's for the $ parameters are those of the
* PK attributes themselves.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -780,14 +780,14 @@ RI_FKey_noaction_del(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
+ * SELECT 1 FROM <fktable> WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo.nkeys; i++)
@@ -968,14 +968,14 @@ RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
+ * SELECT 1 FROM <fktable> WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo.nkeys; i++)
@@ -1489,14 +1489,14 @@ RI_FKey_restrict_del(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
+ * SELECT 1 FROM <fktable> WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo.nkeys; i++)
@@ -1672,14 +1672,14 @@ RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
+ * SELECT 1 FROM <fktable> WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo.nkeys; i++)
@@ -2671,8 +2671,8 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
/*----------
* The query string built is:
- * SELECT fk.keycols FROM ONLY relname fk
- * LEFT OUTER JOIN ONLY pkrelname pk
+ * SELECT fk.keycols FROM relname fk
+ * LEFT OUTER JOIN pkrelname pk
* ON (pk.pkkeycol1=fk.keycol1 [AND ...])
* WHERE pk.pkkeycol1 IS NULL AND
* For MATCH unspecified:
@@ -2695,7 +2695,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
quoteRelationName(pkrelname, pk_rel);
quoteRelationName(fkrelname, fk_rel);
appendStringInfo(&querybuf,
- " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON",
+ " FROM %s fk LEFT OUTER JOIN %s pk ON",
fkrelname, pkrelname);
strcpy(pkattname, "pk.");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
