diff options
| -rw-r--r-- | contrib/postgres_fdw/deparse.c | 41 | ||||
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 165 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 18 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.h | 6 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 61 | ||||
| -rw-r--r-- | doc/src/sgml/fdwhandler.sgml | 28 |
6 files changed, 261 insertions, 58 deletions
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 8068e28184c..6001f4d25ef 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -140,6 +140,7 @@ static void deparseSubqueryTargetList(deparse_expr_cxt *context); static void deparseReturningList(StringInfo buf, RangeTblEntry *rte, Index rtindex, Relation rel, bool trig_after_row, + List *withCheckOptionList, List *returningList, List **retrieved_attrs); static void deparseColumnRef(StringInfo buf, int varno, int varattno, @@ -1645,14 +1646,15 @@ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, * deparse remote INSERT statement * * The statement text is appended to buf, and we also create an integer List - * of the columns being retrieved by RETURNING (if any), which is returned - * to *retrieved_attrs. + * of the columns being retrieved by WITH CHECK OPTION or RETURNING (if any), + * which is returned to *retrieved_attrs. */ void deparseInsertSql(StringInfo buf, RangeTblEntry *rte, Index rtindex, Relation rel, List *targetAttrs, bool doNothing, - List *returningList, List **retrieved_attrs) + List *withCheckOptionList, List *returningList, + List **retrieved_attrs) { AttrNumber pindex; bool first; @@ -1701,20 +1703,21 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte, deparseReturningList(buf, rte, rtindex, rel, rel->trigdesc && rel->trigdesc->trig_insert_after_row, - returningList, retrieved_attrs); + withCheckOptionList, returningList, retrieved_attrs); } /* * deparse remote UPDATE statement * * The statement text is appended to buf, and we also create an integer List - * of the columns being retrieved by RETURNING (if any), which is returned - * to *retrieved_attrs. + * of the columns being retrieved by WITH CHECK OPTION or RETURNING (if any), + * which is returned to *retrieved_attrs. */ void deparseUpdateSql(StringInfo buf, RangeTblEntry *rte, Index rtindex, Relation rel, - List *targetAttrs, List *returningList, + List *targetAttrs, + List *withCheckOptionList, List *returningList, List **retrieved_attrs) { AttrNumber pindex; @@ -1743,7 +1746,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte, deparseReturningList(buf, rte, rtindex, rel, rel->trigdesc && rel->trigdesc->trig_update_after_row, - returningList, retrieved_attrs); + withCheckOptionList, returningList, retrieved_attrs); } /* @@ -1837,7 +1840,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root, &context); else deparseReturningList(buf, rte, rtindex, rel, false, - returningList, retrieved_attrs); + NIL, returningList, retrieved_attrs); } /* @@ -1859,7 +1862,7 @@ deparseDeleteSql(StringInfo buf, RangeTblEntry *rte, deparseReturningList(buf, rte, rtindex, rel, rel->trigdesc && rel->trigdesc->trig_delete_after_row, - returningList, retrieved_attrs); + NIL, returningList, retrieved_attrs); } /* @@ -1921,7 +1924,7 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root, else deparseReturningList(buf, planner_rt_fetch(rtindex, root), rtindex, rel, false, - returningList, retrieved_attrs); + NIL, returningList, retrieved_attrs); } /* @@ -1931,6 +1934,7 @@ static void deparseReturningList(StringInfo buf, RangeTblEntry *rte, Index rtindex, Relation rel, bool trig_after_row, + List *withCheckOptionList, List *returningList, List **retrieved_attrs) { @@ -1943,6 +1947,21 @@ deparseReturningList(StringInfo buf, RangeTblEntry *rte, bms_make_singleton(0 - FirstLowInvalidHeapAttributeNumber); } + if (withCheckOptionList != NIL) + { + /* + * We need the attrs, non-system and system, mentioned in the local + * query's WITH CHECK OPTION list. + * + * Note: we do this to ensure that WCO constraints will be evaluated + * on the data actually inserted/updated on the remote side, which + * might differ from the data supplied by the core code, for example + * as a result of remote triggers. + */ + pull_varattnos((Node *) withCheckOptionList, rtindex, + &attrs_used); + } + if (returningList != NIL) { /* diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index cf4863c5aa2..8c58720df6f 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6173,10 +6173,12 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- =================================================================== -- test WITH CHECK OPTION constraints -- =================================================================== +CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql; CREATE TABLE base_tbl (a int, b int); ALTER TABLE base_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); CREATE FOREIGN TABLE foreign_tbl (a int, b int) - SERVER loopback OPTIONS(table_name 'base_tbl'); + 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 @@ -6192,45 +6194,162 @@ View definition: 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 +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 5); + QUERY PLAN +-------------------------------------------------------------------------------- + Insert on public.foreign_tbl + Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b + -> Result + Output: 0, 5 +(4 rows) + +INSERT INTO rw_view VALUES (0, 5); -- should fail ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (10, 0). +DETAIL: Failing row contains (10, 5). EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down - QUERY PLAN --------------------------------------------------------------------------------------------------- +INSERT INTO rw_view VALUES (0, 15); + QUERY PLAN +-------------------------------------------------------------------------------- + Insert on public.foreign_tbl + Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b + -> Result + Output: 0, 15 +(4 rows) + +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + a | b +----+---- + 10 | 15 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; + QUERY PLAN +--------------------------------------------------------------------------------------- Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> 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 + Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE (5 rows) -UPDATE rw_view SET b = 20 WHERE a = 0; -- ok +UPDATE rw_view SET b = b + 5; -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (20, 20). EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down - QUERY PLAN --------------------------------------------------------------------------------------------------- +UPDATE rw_view SET b = b + 15; + QUERY PLAN +--------------------------------------------------------------------------------------- Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> 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 + Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) 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). +UPDATE rw_view SET b = b + 15; -- ok SELECT * FROM foreign_tbl; - a | b ----+---- - 0 | 20 + a | b +----+---- + 20 | 30 (1 row) DROP FOREIGN TABLE foreign_tbl CASCADE; NOTICE: drop cascades to view rw_view +DROP TRIGGER row_before_insupd_trigger ON base_tbl; DROP TABLE base_tbl; +-- test WCO for partitions +CREATE TABLE child_tbl (a int, b int); +ALTER TABLE child_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); +CREATE FOREIGN TABLE foreign_tbl (a int, b int) + SERVER loopback OPTIONS (table_name 'child_tbl'); +CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a); +ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100); +CREATE VIEW rw_view AS SELECT * FROM parent_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 parent_tbl.a, + parent_tbl.b + FROM parent_tbl + WHERE parent_tbl.a < parent_tbl.b; +Options: check_option=cascaded + +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 5); + QUERY PLAN +----------------------------- + Insert on public.parent_tbl + -> Result + Output: 0, 5 +(3 rows) + +INSERT INTO rw_view VALUES (0, 5); -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (10, 5). +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 15); + QUERY PLAN +----------------------------- + Insert on public.parent_tbl + -> Result + Output: 0, 15 +(3 rows) + +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + a | b +----+---- + 10 | 15 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; + QUERY PLAN +---------------------------------------------------------------------------------------- + Update on public.parent_tbl + Foreign Update on public.foreign_tbl + Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + -> Foreign Scan on public.foreign_tbl + Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE +(6 rows) + +UPDATE rw_view SET b = b + 5; -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (20, 20). +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 15; + QUERY PLAN +---------------------------------------------------------------------------------------- + Update on public.parent_tbl + Foreign Update on public.foreign_tbl + Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + -> Foreign Scan on public.foreign_tbl + Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE +(6 rows) + +UPDATE rw_view SET b = b + 15; -- ok +SELECT * FROM foreign_tbl; + a | b +----+---- + 20 | 30 +(1 row) + +DROP FOREIGN TABLE foreign_tbl CASCADE; +DROP TRIGGER row_before_insupd_trigger ON child_tbl; +DROP TABLE parent_tbl CASCADE; +NOTICE: drop cascades to view rw_view +DROP FUNCTION row_before_insupd_trigfunc; -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 9369cfc2656..5699252091b 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1582,6 +1582,7 @@ postgresPlanForeignModify(PlannerInfo *root, Relation rel; StringInfoData sql; List *targetAttrs = NIL; + List *withCheckOptionList = NIL; List *returningList = NIL; List *retrieved_attrs = NIL; bool doNothing = false; @@ -1631,6 +1632,13 @@ postgresPlanForeignModify(PlannerInfo *root, } /* + * Extract the relevant WITH CHECK OPTION list if any. + */ + if (plan->withCheckOptionLists) + withCheckOptionList = (List *) list_nth(plan->withCheckOptionLists, + subplan_index); + + /* * Extract the relevant RETURNING list if any. */ if (plan->returningLists) @@ -1655,12 +1663,14 @@ postgresPlanForeignModify(PlannerInfo *root, { case CMD_INSERT: deparseInsertSql(&sql, rte, resultRelation, rel, - targetAttrs, doNothing, returningList, + targetAttrs, doNothing, + withCheckOptionList, returningList, &retrieved_attrs); break; case CMD_UPDATE: deparseUpdateSql(&sql, rte, resultRelation, rel, - targetAttrs, returningList, + targetAttrs, + withCheckOptionList, returningList, &retrieved_attrs); break; case CMD_DELETE: @@ -2046,7 +2056,9 @@ postgresBeginForeignInsert(ModifyTableState *mtstate, /* Construct the SQL command string. */ deparseInsertSql(&sql, rte, resultRelation, rel, targetAttrs, doNothing, - resultRelInfo->ri_returningList, &retrieved_attrs); + resultRelInfo->ri_WithCheckOptions, + resultRelInfo->ri_returningList, + &retrieved_attrs); /* Construct an execution state. */ fmstate = create_foreign_modify(mtstate->ps.state, diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index a5d4011e8de..70b538e2f94 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -142,11 +142,13 @@ extern bool is_foreign_expr(PlannerInfo *root, Expr *expr); extern void deparseInsertSql(StringInfo buf, RangeTblEntry *rte, Index rtindex, Relation rel, - List *targetAttrs, bool doNothing, List *returningList, + List *targetAttrs, bool doNothing, + List *withCheckOptionList, List *returningList, List **retrieved_attrs); extern void deparseUpdateSql(StringInfo buf, RangeTblEntry *rte, Index rtindex, Relation rel, - List *targetAttrs, List *returningList, + List *targetAttrs, + List *withCheckOptionList, List *returningList, List **retrieved_attrs); extern void deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index cdfd9c960e5..e1b955f3f0d 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1262,27 +1262,74 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- test WITH CHECK OPTION constraints -- =================================================================== +CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql; + CREATE TABLE base_tbl (a int, b int); ALTER TABLE base_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); CREATE FOREIGN TABLE foreign_tbl (a int, b int) - SERVER loopback OPTIONS(table_name 'base_tbl'); + 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 +INSERT INTO rw_view VALUES (0, 5); +INSERT INTO rw_view VALUES (0, 5); -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 15); +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; +UPDATE rw_view SET b = b + 5; -- 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; -- should fail +UPDATE rw_view SET b = b + 15; +UPDATE rw_view SET b = b + 15; -- ok SELECT * FROM foreign_tbl; DROP FOREIGN TABLE foreign_tbl CASCADE; +DROP TRIGGER row_before_insupd_trigger ON base_tbl; DROP TABLE base_tbl; +-- test WCO for partitions + +CREATE TABLE child_tbl (a int, b int); +ALTER TABLE child_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); +CREATE FOREIGN TABLE foreign_tbl (a int, b int) + SERVER loopback OPTIONS (table_name 'child_tbl'); + +CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a); +ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100); + +CREATE VIEW rw_view AS SELECT * FROM parent_tbl + WHERE a < b WITH CHECK OPTION; +\d+ rw_view + +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 5); +INSERT INTO rw_view VALUES (0, 5); -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 15); +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; +UPDATE rw_view SET b = b + 5; -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 15; +UPDATE rw_view SET b = b + 15; -- ok +SELECT * FROM foreign_tbl; + +DROP FOREIGN TABLE foreign_tbl CASCADE; +DROP TRIGGER row_before_insupd_trigger ON child_tbl; +DROP TABLE parent_tbl CASCADE; + +DROP FUNCTION row_before_insupd_trigfunc; + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index 7b758bdf09b..4ce88dd77c1 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -573,12 +573,14 @@ ExecForeignInsert(EState *estate, <para> The data in the returned slot is used only if the <command>INSERT</command> - query has a <literal>RETURNING</literal> clause or the foreign table has - an <literal>AFTER ROW</literal> trigger. Triggers require all columns, but the - FDW could choose to optimize away returning some or all columns depending - on the contents of the <literal>RETURNING</literal> clause. Regardless, some - slot must be returned to indicate success, or the query's reported row - count will be wrong. + statement has a <literal>RETURNING</literal> clause or involves a view + <literal>WITH CHECK OPTION</literal>; or if the foreign table has + an <literal>AFTER ROW</literal> trigger. Triggers require all columns, + but the FDW could choose to optimize away returning some or all columns + depending on the contents of the <literal>RETURNING</literal> clause or + <literal>WITH CHECK OPTION</literal> constraints. Regardless, some slot + must be returned to indicate success, or the query's reported row count + will be wrong. </para> <para> @@ -619,12 +621,14 @@ ExecForeignUpdate(EState *estate, <para> The data in the returned slot is used only if the <command>UPDATE</command> - query has a <literal>RETURNING</literal> clause or the foreign table has - an <literal>AFTER ROW</literal> trigger. Triggers require all columns, but the - FDW could choose to optimize away returning some or all columns depending - on the contents of the <literal>RETURNING</literal> clause. Regardless, some - slot must be returned to indicate success, or the query's reported row - count will be wrong. + statement has a <literal>RETURNING</literal> clause or involves a view + <literal>WITH CHECK OPTION</literal>; or if the foreign table has + an <literal>AFTER ROW</literal> trigger. Triggers require all columns, + but the FDW could choose to optimize away returning some or all columns + depending on the contents of the <literal>RETURNING</literal> clause or + <literal>WITH CHECK OPTION</literal> constraints. Regardless, some slot + must be returned to indicate success, or the query's reported row count + will be wrong. </para> <para> |