From b32e63506cfec8c8bd3237ec5043de7414564d10 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 29 Jun 2016 16:02:08 -0400 Subject: [PATCH] Fix match_foreign_keys_to_quals for FKs linking to unused rtable entries. Since get_relation_foreign_keys doesn't try to determine whether RTEs are actually part of the query semantics, it might make FK info records linking to RTEs that won't have a RelOptInfo at all. Cope with that. Per bug #14219 from Andrew Gierth. Report: <20160629183338.1397.43514@wrigleys.postgresql.org> --- src/backend/optimizer/plan/initsplan.c | 19 +++++++++++++++++-- src/test/regress/expected/foreign_key.out | 22 ++++++++++++++++++++++ src/test/regress/sql/foreign_key.sql | 10 ++++++++++ 3 files changed, 49 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index db8db75b6e..84ce6b3125 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -2329,10 +2329,25 @@ match_foreign_keys_to_quals(PlannerInfo *root) foreach(lc, root->fkey_list) { ForeignKeyOptInfo *fkinfo = (ForeignKeyOptInfo *) lfirst(lc); - RelOptInfo *con_rel = find_base_rel(root, fkinfo->con_relid); - RelOptInfo *ref_rel = find_base_rel(root, fkinfo->ref_relid); + RelOptInfo *con_rel; + RelOptInfo *ref_rel; int colno; + /* + * Either relid might identify a rel that is in the query's rtable but + * isn't referenced by the jointree so won't have a RelOptInfo. Hence + * don't use find_base_rel() here. We can ignore such FKs. + */ + if (fkinfo->con_relid >= root->simple_rel_array_size || + fkinfo->ref_relid >= root->simple_rel_array_size) + continue; /* just paranoia */ + con_rel = root->simple_rel_array[fkinfo->con_relid]; + if (con_rel == NULL) + continue; + ref_rel = root->simple_rel_array[fkinfo->ref_relid]; + if (ref_rel == NULL) + continue; + /* * Ignore FK unless both rels are baserels. This gets rid of FKs that * link to inheritance child rels (otherrels) and those that link to diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 8c47babb6d..044881af71 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1359,3 +1359,25 @@ update pp set f1=f1+1; -- fail ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc" DETAIL: Key (f1)=(13) is still referenced from table "cc". drop table pp, cc; +-- +-- Test interaction of foreign-key optimization with rules (bug #14219) +-- +create temp table t1 (a integer primary key, b text); +create temp table t2 (a integer primary key, b integer references t1); +create rule r1 as on delete to t1 do delete from t2 where t2.b = old.a; +explain (costs off) delete from t1 where a = 1; + QUERY PLAN +-------------------------------------------- + Delete on t2 + -> Nested Loop + -> Index Scan using t1_pkey on t1 + Index Cond: (a = 1) + -> Seq Scan on t2 + Filter: (b = 1) + + Delete on t1 + -> Index Scan using t1_pkey on t1 + Index Cond: (a = 1) +(10 rows) + +delete from t1 where a = 1; diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 53276e4d67..85c9d04d64 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1009,3 +1009,13 @@ update pp set f1=f1+1; insert into cc values(13); update pp set f1=f1+1; -- fail drop table pp, cc; + +-- +-- Test interaction of foreign-key optimization with rules (bug #14219) +-- +create temp table t1 (a integer primary key, b text); +create temp table t2 (a integer primary key, b integer references t1); +create rule r1 as on delete to t1 do delete from t2 where t2.b = old.a; + +explain (costs off) delete from t1 where a = 1; +delete from t1 where a = 1; -- 2.40.0