From c4bee09c0e1fc7fb2281ba1e125ab17c5e9a20e7 Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Mon, 22 Sep 2014 20:22:16 -0400 Subject: [PATCH] Process withCheckOption exprs in setrefs.c While withCheckOption exprs had been handled in many cases by happenstance, they need to be handled during set_plan_references and more specifically down in set_plan_refs for ModifyTable plan nodes. This is to ensure that the opfuncid's are set for operators referenced in the withCheckOption exprs. Identified as an issue by Thom Brown Patch by Dean Rasheed Back-patch to 9.4, where withCheckOption was introduced. --- src/backend/optimizer/plan/setrefs.c | 3 +++ src/test/regress/expected/updatable_views.out | 20 +++++++++++++++++++ src/test/regress/sql/updatable_views.sql | 19 ++++++++++++++++++ 3 files changed, 42 insertions(+) diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 768c5c7670..a273e32c29 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -693,6 +693,9 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) Assert(splan->plan.targetlist == NIL); Assert(splan->plan.qual == NIL); + splan->withCheckOptionLists = + fix_scan_list(root, splan->withCheckOptionLists, rtoffset); + if (splan->returningLists) { List *newRL = NIL; diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 6576c47451..9ed48962b3 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -1567,6 +1567,26 @@ NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 drop cascades to view rw_view3 +-- WITH CHECK OPTION with scalar array ops +CREATE TABLE base_tbl (a int, b int[]); +CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a = ANY (b) + WITH CHECK OPTION; +INSERT INTO rw_view1 VALUES (1, ARRAY[1,2,3]); -- ok +INSERT INTO rw_view1 VALUES (10, ARRAY[4,5]); -- should fail +ERROR: new row violates WITH CHECK OPTION for view "rw_view1" +DETAIL: Failing row contains (10, {4,5}). +UPDATE rw_view1 SET b[2] = -b[2] WHERE a = 1; -- ok +UPDATE rw_view1 SET b[1] = -b[1] WHERE a = 1; -- should fail +ERROR: new row violates WITH CHECK OPTION for view "rw_view1" +DETAIL: Failing row contains (1, {-1,-2,3}). +PREPARE ins(int, int[]) AS INSERT INTO rw_view1 VALUES($1, $2); +EXECUTE ins(2, ARRAY[1,2,3]); -- ok +EXECUTE ins(10, ARRAY[4,5]); -- should fail +ERROR: new row violates WITH CHECK OPTION for view "rw_view1" +DETAIL: Failing row contains (10, {4,5}). +DEALLOCATE PREPARE ins; +DROP TABLE base_tbl CASCADE; +NOTICE: drop cascades to view rw_view1 -- WITH CHECK OPTION with subquery CREATE TABLE base_tbl (a int); CREATE TABLE ref_tbl (a int PRIMARY KEY); diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index c072fca6be..60c7e29221 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -707,6 +707,25 @@ INSERT INTO rw_view3 VALUES (3); -- ok DROP TABLE base_tbl CASCADE; +-- WITH CHECK OPTION with scalar array ops + +CREATE TABLE base_tbl (a int, b int[]); +CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a = ANY (b) + WITH CHECK OPTION; + +INSERT INTO rw_view1 VALUES (1, ARRAY[1,2,3]); -- ok +INSERT INTO rw_view1 VALUES (10, ARRAY[4,5]); -- should fail + +UPDATE rw_view1 SET b[2] = -b[2] WHERE a = 1; -- ok +UPDATE rw_view1 SET b[1] = -b[1] WHERE a = 1; -- should fail + +PREPARE ins(int, int[]) AS INSERT INTO rw_view1 VALUES($1, $2); +EXECUTE ins(2, ARRAY[1,2,3]); -- ok +EXECUTE ins(10, ARRAY[4,5]); -- should fail +DEALLOCATE PREPARE ins; + +DROP TABLE base_tbl CASCADE; + -- WITH CHECK OPTION with subquery CREATE TABLE base_tbl (a int); -- 2.40.0