On 2017/07/21 3:24, Robert Haas wrote:
I think that's reasonable. This should be committed and back-patched
to 9.6, right?
Yeah, because direct modify was introduced in 9.6.
Attached is the second version which updated docs in postgres-fdw.sgml
as well.
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 5856,5861 **** INSERT INTO ft1(c1, c2) VALUES(1111, 2);
--- 5856,5921 ----
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 | Collation | Nullable | Default | 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);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 1158,1163 **** UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
--- 1158,1187 ----
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);
*** a/doc/src/sgml/postgres-fdw.sgml
--- b/doc/src/sgml/postgres-fdw.sgml
***************
*** 498,505 ****
<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</>,
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.
--- 498,507 ----
<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, 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.
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 6499,6506 **** make_modifytable(PlannerInfo *root,
}
/*
! * If the target foreign table has any row-level triggers, we
can't
! * modify the foreign table directly.
*/
direct_modify = false;
if (fdwroutine != NULL &&
--- 6499,6508 ----
}
/*
! * 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 &&
***************
*** 6508,6513 **** make_modifytable(PlannerInfo *root,
--- 6510,6516 ----
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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
