Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb32e635

Browse files
committed
Fix match_foreign_keys_to_quals for FKs linking to unused rtable entries.
Since get_relation_foreign_keys doesn't try to determine whether RTEsare actually part of the query semantics, it might make FK info recordslinking 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>
1 parent4242a71 commitb32e635

File tree

3 files changed

+49
-2
lines changed

3 files changed

+49
-2
lines changed

‎src/backend/optimizer/plan/initsplan.c

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2329,10 +2329,25 @@ match_foreign_keys_to_quals(PlannerInfo *root)
23292329
foreach(lc,root->fkey_list)
23302330
{
23312331
ForeignKeyOptInfo*fkinfo= (ForeignKeyOptInfo*)lfirst(lc);
2332-
RelOptInfo*con_rel=find_base_rel(root,fkinfo->con_relid);
2333-
RelOptInfo*ref_rel=find_base_rel(root,fkinfo->ref_relid);
2332+
RelOptInfo*con_rel;
2333+
RelOptInfo*ref_rel;
23342334
intcolno;
23352335

2336+
/*
2337+
* Either relid might identify a rel that is in the query's rtable but
2338+
* isn't referenced by the jointree so won't have a RelOptInfo. Hence
2339+
* don't use find_base_rel() here. We can ignore such FKs.
2340+
*/
2341+
if (fkinfo->con_relid >=root->simple_rel_array_size||
2342+
fkinfo->ref_relid >=root->simple_rel_array_size)
2343+
continue;/* just paranoia */
2344+
con_rel=root->simple_rel_array[fkinfo->con_relid];
2345+
if (con_rel==NULL)
2346+
continue;
2347+
ref_rel=root->simple_rel_array[fkinfo->ref_relid];
2348+
if (ref_rel==NULL)
2349+
continue;
2350+
23362351
/*
23372352
* Ignore FK unless both rels are baserels. This gets rid of FKs that
23382353
* link to inheritance child rels (otherrels) and those that link to

‎src/test/regress/expected/foreign_key.out

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1359,3 +1359,25 @@ update pp set f1=f1+1; -- fail
13591359
ERROR: update or delete on table "pp" violates foreign key constraint "cc_f1_fkey" on table "cc"
13601360
DETAIL: Key (f1)=(13) is still referenced from table "cc".
13611361
drop table pp, cc;
1362+
--
1363+
-- Test interaction of foreign-key optimization with rules (bug #14219)
1364+
--
1365+
create temp table t1 (a integer primary key, b text);
1366+
create temp table t2 (a integer primary key, b integer references t1);
1367+
create rule r1 as on delete to t1 do delete from t2 where t2.b = old.a;
1368+
explain (costs off) delete from t1 where a = 1;
1369+
QUERY PLAN
1370+
--------------------------------------------
1371+
Delete on t2
1372+
-> Nested Loop
1373+
-> Index Scan using t1_pkey on t1
1374+
Index Cond: (a = 1)
1375+
-> Seq Scan on t2
1376+
Filter: (b = 1)
1377+
1378+
Delete on t1
1379+
-> Index Scan using t1_pkey on t1
1380+
Index Cond: (a = 1)
1381+
(10 rows)
1382+
1383+
delete from t1 where a = 1;

‎src/test/regress/sql/foreign_key.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1009,3 +1009,13 @@ update pp set f1=f1+1;
10091009
insert into ccvalues(13);
10101010
update ppset f1=f1+1;-- fail
10111011
droptable pp, cc;
1012+
1013+
--
1014+
-- Test interaction of foreign-key optimization with rules (bug #14219)
1015+
--
1016+
create temp table t1 (aintegerprimary key, btext);
1017+
create temp table t2 (aintegerprimary key, bintegerreferences t1);
1018+
createruler1ason delete to t1 dodeletefrom t2wheret2.b=old.a;
1019+
1020+
explain (costs off)deletefrom t1where a=1;
1021+
deletefrom t1where a=1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp