diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 36 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 23 | ||||
| -rw-r--r-- | src/backend/optimizer/util/inherit.c | 22 |
3 files changed, 68 insertions, 13 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index bd706d18d1b..1a2c2a665c6 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6768,6 +6768,42 @@ 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; +-- Try a more complex permutation of WCO where there are multiple levels of +-- partitioned tables with columns not all in the same order +CREATE TABLE parent_tbl (a int, b text, c numeric) PARTITION BY RANGE(a); +CREATE TABLE sub_parent (c numeric, a int, b text) PARTITION BY RANGE(a); +ALTER TABLE parent_tbl ATTACH PARTITION sub_parent FOR VALUES FROM (1) TO (10); +CREATE TABLE child_local (b text, c numeric, a int); +CREATE FOREIGN TABLE child_foreign (b text, c numeric, a int) + SERVER loopback OPTIONS (table_name 'child_local'); +ALTER TABLE sub_parent ATTACH PARTITION child_foreign FOR VALUES FROM (1) TO (10); +CREATE VIEW rw_view AS SELECT * FROM parent_tbl WHERE a < 5 WITH CHECK OPTION; +INSERT INTO parent_tbl (a) VALUES(1),(5); +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = 'text', c = 123.456; + QUERY PLAN +------------------------------------------------------------------------------------------------- + Update on public.parent_tbl + Foreign Update on public.child_foreign parent_tbl_1 + Remote SQL: UPDATE public.child_local SET b = $2, c = $3 WHERE ctid = $1 RETURNING a + -> Foreign Scan on public.child_foreign parent_tbl_1 + Output: 'text'::text, 123.456, parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* + Remote SQL: SELECT b, c, a, ctid FROM public.child_local WHERE ((a < 5)) FOR UPDATE +(6 rows) + +UPDATE rw_view SET b = 'text', c = 123.456; +SELECT * FROM parent_tbl ORDER BY a; + a | b | c +---+------+--------- + 1 | text | 123.456 + 5 | | +(2 rows) + +DROP VIEW rw_view; +DROP TABLE child_local; +DROP FOREIGN TABLE child_foreign; +DROP TABLE sub_parent; +DROP TABLE parent_tbl; -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 2e6f7f4852c..94fe69ed3b6 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1580,6 +1580,29 @@ DROP TABLE parent_tbl CASCADE; DROP FUNCTION row_before_insupd_trigfunc; +-- Try a more complex permutation of WCO where there are multiple levels of +-- partitioned tables with columns not all in the same order +CREATE TABLE parent_tbl (a int, b text, c numeric) PARTITION BY RANGE(a); +CREATE TABLE sub_parent (c numeric, a int, b text) PARTITION BY RANGE(a); +ALTER TABLE parent_tbl ATTACH PARTITION sub_parent FOR VALUES FROM (1) TO (10); +CREATE TABLE child_local (b text, c numeric, a int); +CREATE FOREIGN TABLE child_foreign (b text, c numeric, a int) + SERVER loopback OPTIONS (table_name 'child_local'); +ALTER TABLE sub_parent ATTACH PARTITION child_foreign FOR VALUES FROM (1) TO (10); +CREATE VIEW rw_view AS SELECT * FROM parent_tbl WHERE a < 5 WITH CHECK OPTION; + +INSERT INTO parent_tbl (a) VALUES(1),(5); +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = 'text', c = 123.456; +UPDATE rw_view SET b = 'text', c = 123.456; +SELECT * FROM parent_tbl ORDER BY a; + +DROP VIEW rw_view; +DROP TABLE child_local; +DROP FOREIGN TABLE child_foreign; +DROP TABLE sub_parent; +DROP TABLE parent_tbl; + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c index f51ce45cd3b..d3b0b0d2af9 100644 --- a/src/backend/optimizer/util/inherit.c +++ b/src/backend/optimizer/util/inherit.c @@ -52,7 +52,7 @@ static Bitmapset *translate_col_privs(const Bitmapset *parent_privs, static Bitmapset *translate_col_privs_multilevel(PlannerInfo *root, RelOptInfo *rel, RelOptInfo *top_parent_rel, - Bitmapset *top_parent_cols); + Bitmapset *parent_cols); static void expand_appendrel_subquery(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte, Index rti); @@ -923,28 +923,26 @@ apply_child_basequals(PlannerInfo *root, RelOptInfo *parentrel, /* * translate_col_privs_multilevel - * Recursively translates the column numbers contained in - * 'top_parent_cols' to the columns numbers of a descendent relation - * given by 'relid' + * Recursively translates the column numbers contained in 'parent_cols' + * to the columns numbers of a descendent relation given by 'rel' */ static Bitmapset * translate_col_privs_multilevel(PlannerInfo *root, RelOptInfo *rel, RelOptInfo *top_parent_rel, - Bitmapset *top_parent_cols) + Bitmapset *parent_cols) { - Bitmapset *result; AppendRelInfo *appinfo; - if (top_parent_cols == NULL) + if (parent_cols == NULL) return NULL; /* Recurse if immediate parent is not the top parent. */ if (rel->parent != top_parent_rel) { if (rel->parent) - result = translate_col_privs_multilevel(root, rel->parent, - top_parent_rel, - top_parent_cols); + parent_cols = translate_col_privs_multilevel(root, rel->parent, + top_parent_rel, + parent_cols); else elog(ERROR, "rel with relid %u is not a child rel", rel->relid); } @@ -953,7 +951,5 @@ translate_col_privs_multilevel(PlannerInfo *root, RelOptInfo *rel, appinfo = root->append_rel_array[rel->relid]; Assert(appinfo != NULL); - result = translate_col_privs(top_parent_cols, appinfo->translated_vars); - - return result; + return translate_col_privs(parent_cols, appinfo->translated_vars); } |