aboutsummaryrefslogtreecommitdiff
path: root/contrib/postgres_fdw
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/postgres_fdw')
-rw-r--r--contrib/postgres_fdw/deparse.c41
-rw-r--r--contrib/postgres_fdw/expected/postgres_fdw.out165
-rw-r--r--contrib/postgres_fdw/postgres_fdw.c18
-rw-r--r--contrib/postgres_fdw/postgres_fdw.h6
-rw-r--r--contrib/postgres_fdw/sql/postgres_fdw.sql61
5 files changed, 245 insertions, 46 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)
-- ===================================================================
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-03 16:47:27 +0000