diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 60 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 24 | ||||
| -rw-r--r-- | doc/src/sgml/postgres-fdw.sgml | 6 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 7 |
4 files changed, 93 insertions, 4 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index e9addade266..20ea7c03087 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -4653,6 +4653,66 @@ INSERT INTO ft1(c1, c2) VALUES(1111, 2); UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- =================================================================== +-- test WITH CHECK OPTION constraints +-- =================================================================== +CREATE TABLE base_tbl (a int, b int); +CREATE FOREIGN TABLE foreign_tbl (a int, b int) + SERVER loopback OPTIONS(table_name 'base_tbl'); +CREATE VIEW rw_view AS SELECT * FROM foreign_tbl + WHERE a < b WITH CHECK OPTION; +\d+ rw_view + View "public.rw_view" + Column | Type | Modifiers | Storage | Description +--------+---------+-----------+---------+------------- + a | integer | | plain | + b | integer | | plain | +View definition: + SELECT foreign_tbl.a, + foreign_tbl.b + FROM foreign_tbl + WHERE foreign_tbl.a < foreign_tbl.b; +Options: check_option=cascaded + +INSERT INTO rw_view VALUES (0, 10); -- ok +INSERT INTO rw_view VALUES (10, 0); -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (10, 0). +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down + QUERY PLAN +-------------------------------------------------------------------------------------------------- + Update on public.foreign_tbl + Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 + -> Foreign Scan on public.foreign_tbl + Output: foreign_tbl.a, 20, foreign_tbl.ctid + Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE +(5 rows) + +UPDATE rw_view SET b = 20 WHERE a = 0; -- ok +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down + QUERY PLAN +-------------------------------------------------------------------------------------------------- + Update on public.foreign_tbl + Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 + -> Foreign Scan on public.foreign_tbl + Output: foreign_tbl.a, '-20'::integer, foreign_tbl.ctid + Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE +(5 rows) + +UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (0, -20). +SELECT * FROM foreign_tbl; + a | b +---+---- + 0 | 20 +(1 row) + +DROP FOREIGN TABLE foreign_tbl CASCADE; +NOTICE: drop cascades to view rw_view +DROP TABLE base_tbl; +-- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== create table loc1 (f1 serial, f2 text); diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index a43115a35ac..525da8c95fd 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -843,6 +843,30 @@ UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- =================================================================== +-- test WITH CHECK OPTION constraints +-- =================================================================== + +CREATE TABLE base_tbl (a int, b int); +CREATE FOREIGN TABLE foreign_tbl (a int, b int) + SERVER loopback OPTIONS(table_name 'base_tbl'); +CREATE VIEW rw_view AS SELECT * FROM foreign_tbl + WHERE a < b WITH CHECK OPTION; +\d+ rw_view + +INSERT INTO rw_view VALUES (0, 10); -- ok +INSERT INTO rw_view VALUES (10, 0); -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down +UPDATE rw_view SET b = 20 WHERE a = 0; -- ok +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down +UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail +SELECT * FROM foreign_tbl; + +DROP FOREIGN TABLE foreign_tbl CASCADE; +DROP TABLE base_tbl; + +-- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== create table loc1 (f1 serial, f2 text); diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index b31f3731e49..a6c56d1f634 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -488,8 +488,10 @@ <filename>postgres_fdw</> attempts to optimize the query execution by sending the whole query to the remote server if there are no query <literal>WHERE</> clauses that cannot be sent to the remote server, - no local joins for the query, and no row-level local <literal>BEFORE</> or - <literal>AFTER</> triggers on the target table. In <command>UPDATE</>, + no local joins for the query, no row-level local <literal>BEFORE</> or + <literal>AFTER</> triggers on the target table, and no + <literal>CHECK OPTION</> constraints from parent views. + In <command>UPDATE</>, expressions to assign to target columns must use only built-in data types, <literal>IMMUTABLE</> operators, or <literal>IMMUTABLE</> functions, to reduce the risk of misexecution of the query. diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 6f1166b7039..e230329a452 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -6145,8 +6145,10 @@ make_modifytable(PlannerInfo *root, } /* - * If the target foreign table has any row-level triggers, we can't - * modify the foreign table directly. + * Try to modify the foreign table directly if (1) the FDW provides + * callback functions needed for that, (2) there are no row-level + * triggers on the foreign table, and (3) there are no WITH CHECK + * OPTIONs from parent views. */ direct_modify = false; if (fdwroutine != NULL && @@ -6154,6 +6156,7 @@ make_modifytable(PlannerInfo *root, fdwroutine->BeginDirectModify != NULL && fdwroutine->IterateDirectModify != NULL && fdwroutine->EndDirectModify != NULL && + withCheckOptionLists == NIL && !has_row_triggers(root, rti, operation)) direct_modify = fdwroutine->PlanDirectModify(root, node, rti, i); if (direct_modify) |