Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita45adc7

Browse files
committed
Fix WITH CHECK OPTION on views referencing postgres_fdw tables.
If a view references a foreign table, and the foreign table has aBEFORE INSERT trigger, then it's possible for a tuple inserted orupdated through the view to be changed such that it violates theview's WITH CHECK OPTION constraint.Before this commit, postgres_fdw handled this case inconsistently. ARETURNING clause on the INSERT or UPDATE statement targeting the viewwould cause the finally-inserted tuple to be read back, and the WITHCHECK OPTION violation would throw an error. But without a RETURNINGclause, postgres_fdw would not read the final tuple back, and WITHCHECK OPTION would not throw an error for the violation (or may throwan error when there is no real violation). AFTER ROW triggers on theforeign table had a similar effect as a RETURNING clause on the INSERTor UPDATE statement.To fix, this commit retrieves the attributes needed to enforce theWITH CHECK OPTION constraint along with the attributes needed for theRETURNING clause (if any) from the remote side. Thus, the WITH CHECKOPTION constraint is always evaluated against the final tuple afterany triggers on the remote side.This fix may be considered inconsistent with CHECK constraintsdeclared on foreign tables, which are not enforced locally at all(because the constraint is on a remote object). The discussionconcluded that this difference is reasonable, because the WITH CHECKOPTION is a constraint on the local view (not any remote object);therefore it only makes sense to enforce its WITH CHECK OPTIONconstraint locally.Author: Etsuro FujitaReviewed-by: Arthur Zakirov, Stephen FrostDiscussion:https://www.postgresql.org/message-id/7eb58fab-fd3b-781b-ac33-f7cfec96021f%40lab.ntt.co.jp
1 parente915fed commita45adc7

File tree

6 files changed

+261
-58
lines changed

6 files changed

+261
-58
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 30 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,7 @@ static void deparseSubqueryTargetList(deparse_expr_cxt *context);
140140
staticvoiddeparseReturningList(StringInfobuf,RangeTblEntry*rte,
141141
Indexrtindex,Relationrel,
142142
booltrig_after_row,
143+
List*withCheckOptionList,
143144
List*returningList,
144145
List**retrieved_attrs);
145146
staticvoiddeparseColumnRef(StringInfobuf,intvarno,intvarattno,
@@ -1645,14 +1646,15 @@ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
16451646
* deparse remote INSERT statement
16461647
*
16471648
* The statement text is appended to buf, and we also create an integer List
1648-
* of the columns being retrieved by RETURNING (if any), which is returned
1649-
* to *retrieved_attrs.
1649+
* of the columns being retrieved byWITH CHECK OPTION orRETURNING (if any),
1650+
*which is returnedto *retrieved_attrs.
16501651
*/
16511652
void
16521653
deparseInsertSql(StringInfobuf,RangeTblEntry*rte,
16531654
Indexrtindex,Relationrel,
16541655
List*targetAttrs,booldoNothing,
1655-
List*returningList,List**retrieved_attrs)
1656+
List*withCheckOptionList,List*returningList,
1657+
List**retrieved_attrs)
16561658
{
16571659
AttrNumberpindex;
16581660
boolfirst;
@@ -1701,20 +1703,21 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
17011703

17021704
deparseReturningList(buf,rte,rtindex,rel,
17031705
rel->trigdesc&&rel->trigdesc->trig_insert_after_row,
1704-
returningList,retrieved_attrs);
1706+
withCheckOptionList,returningList,retrieved_attrs);
17051707
}
17061708

17071709
/*
17081710
* deparse remote UPDATE statement
17091711
*
17101712
* The statement text is appended to buf, and we also create an integer List
1711-
* of the columns being retrieved by RETURNING (if any), which is returned
1712-
* to *retrieved_attrs.
1713+
* of the columns being retrieved byWITH CHECK OPTION orRETURNING (if any),
1714+
*which is returnedto *retrieved_attrs.
17131715
*/
17141716
void
17151717
deparseUpdateSql(StringInfobuf,RangeTblEntry*rte,
17161718
Indexrtindex,Relationrel,
1717-
List*targetAttrs,List*returningList,
1719+
List*targetAttrs,
1720+
List*withCheckOptionList,List*returningList,
17181721
List**retrieved_attrs)
17191722
{
17201723
AttrNumberpindex;
@@ -1743,7 +1746,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
17431746

17441747
deparseReturningList(buf,rte,rtindex,rel,
17451748
rel->trigdesc&&rel->trigdesc->trig_update_after_row,
1746-
returningList,retrieved_attrs);
1749+
withCheckOptionList,returningList,retrieved_attrs);
17471750
}
17481751

17491752
/*
@@ -1837,7 +1840,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
18371840
&context);
18381841
else
18391842
deparseReturningList(buf,rte,rtindex,rel, false,
1840-
returningList,retrieved_attrs);
1843+
NIL,returningList,retrieved_attrs);
18411844
}
18421845

18431846
/*
@@ -1859,7 +1862,7 @@ deparseDeleteSql(StringInfo buf, RangeTblEntry *rte,
18591862

18601863
deparseReturningList(buf,rte,rtindex,rel,
18611864
rel->trigdesc&&rel->trigdesc->trig_delete_after_row,
1862-
returningList,retrieved_attrs);
1865+
NIL,returningList,retrieved_attrs);
18631866
}
18641867

18651868
/*
@@ -1921,7 +1924,7 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
19211924
else
19221925
deparseReturningList(buf,planner_rt_fetch(rtindex,root),
19231926
rtindex,rel, false,
1924-
returningList,retrieved_attrs);
1927+
NIL,returningList,retrieved_attrs);
19251928
}
19261929

19271930
/*
@@ -1931,6 +1934,7 @@ static void
19311934
deparseReturningList(StringInfobuf,RangeTblEntry*rte,
19321935
Indexrtindex,Relationrel,
19331936
booltrig_after_row,
1937+
List*withCheckOptionList,
19341938
List*returningList,
19351939
List**retrieved_attrs)
19361940
{
@@ -1943,6 +1947,21 @@ deparseReturningList(StringInfo buf, RangeTblEntry *rte,
19431947
bms_make_singleton(0-FirstLowInvalidHeapAttributeNumber);
19441948
}
19451949

1950+
if (withCheckOptionList!=NIL)
1951+
{
1952+
/*
1953+
* We need the attrs, non-system and system, mentioned in the local
1954+
* query's WITH CHECK OPTION list.
1955+
*
1956+
* Note: we do this to ensure that WCO constraints will be evaluated
1957+
* on the data actually inserted/updated on the remote side, which
1958+
* might differ from the data supplied by the core code, for example
1959+
* as a result of remote triggers.
1960+
*/
1961+
pull_varattnos((Node*)withCheckOptionList,rtindex,
1962+
&attrs_used);
1963+
}
1964+
19461965
if (returningList!=NIL)
19471966
{
19481967
/*

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 142 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -6173,10 +6173,12 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
61736173
-- ===================================================================
61746174
-- test WITH CHECK OPTION constraints
61756175
-- ===================================================================
6176+
CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql;
61766177
CREATE TABLE base_tbl (a int, b int);
61776178
ALTER TABLE base_tbl SET (autovacuum_enabled = 'false');
6179+
CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc();
61786180
CREATE FOREIGN TABLE foreign_tbl (a int, b int)
6179-
SERVER loopback OPTIONS(table_name 'base_tbl');
6181+
SERVER loopback OPTIONS(table_name 'base_tbl');
61806182
CREATE VIEW rw_view AS SELECT * FROM foreign_tbl
61816183
WHERE a < b WITH CHECK OPTION;
61826184
\d+ rw_view
@@ -6192,45 +6194,162 @@ View definition:
61926194
WHERE foreign_tbl.a < foreign_tbl.b;
61936195
Options: check_option=cascaded
61946196

6195-
INSERT INTO rw_view VALUES (0, 10); -- ok
6196-
INSERT INTO rw_view VALUES (10, 0); -- should fail
6197+
EXPLAIN (VERBOSE, COSTS OFF)
6198+
INSERT INTO rw_view VALUES (0, 5);
6199+
QUERY PLAN
6200+
--------------------------------------------------------------------------------
6201+
Insert on public.foreign_tbl
6202+
Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b
6203+
-> Result
6204+
Output: 0, 5
6205+
(4 rows)
6206+
6207+
INSERT INTO rw_view VALUES (0, 5); -- should fail
61976208
ERROR: new row violates check option for view "rw_view"
6198-
DETAIL: Failing row contains (10,0).
6209+
DETAIL: Failing row contains (10,5).
61996210
EXPLAIN (VERBOSE, COSTS OFF)
6200-
UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down
6201-
QUERY PLAN
6202-
--------------------------------------------------------------------------------------------------
6211+
INSERT INTO rw_view VALUES (0, 15);
6212+
QUERY PLAN
6213+
--------------------------------------------------------------------------------
6214+
Insert on public.foreign_tbl
6215+
Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b
6216+
-> Result
6217+
Output: 0, 15
6218+
(4 rows)
6219+
6220+
INSERT INTO rw_view VALUES (0, 15); -- ok
6221+
SELECT * FROM foreign_tbl;
6222+
a | b
6223+
----+----
6224+
10 | 15
6225+
(1 row)
6226+
6227+
EXPLAIN (VERBOSE, COSTS OFF)
6228+
UPDATE rw_view SET b = b + 5;
6229+
QUERY PLAN
6230+
---------------------------------------------------------------------------------------
62036231
Update on public.foreign_tbl
6204-
Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
6232+
Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
62056233
-> Foreign Scan on public.foreign_tbl
6206-
Output: foreign_tbl.a,20, foreign_tbl.ctid
6207-
Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE
6234+
Output: foreign_tbl.a,(foreign_tbl.b + 5), foreign_tbl.ctid
6235+
Remote SQL: SELECT a,b,ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
62086236
(5 rows)
62096237

6210-
UPDATE rw_view SET b = 20 WHERE a = 0; -- ok
6238+
UPDATE rw_view SET b = b + 5; -- should fail
6239+
ERROR: new row violates check option for view "rw_view"
6240+
DETAIL: Failing row contains (20, 20).
62116241
EXPLAIN (VERBOSE, COSTS OFF)
6212-
UPDATE rw_view SET b =-20 WHERE a = 0; -- not pushed down
6213-
QUERY PLAN
6214-
--------------------------------------------------------------------------------------------------
6242+
UPDATE rw_view SET b =b + 15;
6243+
QUERY PLAN
6244+
---------------------------------------------------------------------------------------
62156245
Update on public.foreign_tbl
6216-
Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
6246+
Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
62176247
-> Foreign Scan on public.foreign_tbl
6218-
Output: foreign_tbl.a,'-20'::integer, foreign_tbl.ctid
6219-
Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE
6248+
Output: foreign_tbl.a,(foreign_tbl.b + 15), foreign_tbl.ctid
6249+
Remote SQL: SELECT a,b,ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
62206250
(5 rows)
62216251

6222-
UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail
6223-
ERROR: new row violates check option for view "rw_view"
6224-
DETAIL: Failing row contains (0, -20).
6252+
UPDATE rw_view SET b = b + 15; -- ok
62256253
SELECT * FROM foreign_tbl;
6226-
a | b
6227-
---+----
6228-
0 |20
6254+
a| b
6255+
----+----
6256+
20 |30
62296257
(1 row)
62306258

62316259
DROP FOREIGN TABLE foreign_tbl CASCADE;
62326260
NOTICE: drop cascades to view rw_view
6261+
DROP TRIGGER row_before_insupd_trigger ON base_tbl;
62336262
DROP TABLE base_tbl;
6263+
-- test WCO for partitions
6264+
CREATE TABLE child_tbl (a int, b int);
6265+
ALTER TABLE child_tbl SET (autovacuum_enabled = 'false');
6266+
CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc();
6267+
CREATE FOREIGN TABLE foreign_tbl (a int, b int)
6268+
SERVER loopback OPTIONS (table_name 'child_tbl');
6269+
CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a);
6270+
ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100);
6271+
CREATE VIEW rw_view AS SELECT * FROM parent_tbl
6272+
WHERE a < b WITH CHECK OPTION;
6273+
\d+ rw_view
6274+
View "public.rw_view"
6275+
Column | Type | Collation | Nullable | Default | Storage | Description
6276+
--------+---------+-----------+----------+---------+---------+-------------
6277+
a | integer | | | | plain |
6278+
b | integer | | | | plain |
6279+
View definition:
6280+
SELECT parent_tbl.a,
6281+
parent_tbl.b
6282+
FROM parent_tbl
6283+
WHERE parent_tbl.a < parent_tbl.b;
6284+
Options: check_option=cascaded
6285+
6286+
EXPLAIN (VERBOSE, COSTS OFF)
6287+
INSERT INTO rw_view VALUES (0, 5);
6288+
QUERY PLAN
6289+
-----------------------------
6290+
Insert on public.parent_tbl
6291+
-> Result
6292+
Output: 0, 5
6293+
(3 rows)
6294+
6295+
INSERT INTO rw_view VALUES (0, 5); -- should fail
6296+
ERROR: new row violates check option for view "rw_view"
6297+
DETAIL: Failing row contains (10, 5).
6298+
EXPLAIN (VERBOSE, COSTS OFF)
6299+
INSERT INTO rw_view VALUES (0, 15);
6300+
QUERY PLAN
6301+
-----------------------------
6302+
Insert on public.parent_tbl
6303+
-> Result
6304+
Output: 0, 15
6305+
(3 rows)
6306+
6307+
INSERT INTO rw_view VALUES (0, 15); -- ok
6308+
SELECT * FROM foreign_tbl;
6309+
a | b
6310+
----+----
6311+
10 | 15
6312+
(1 row)
6313+
6314+
EXPLAIN (VERBOSE, COSTS OFF)
6315+
UPDATE rw_view SET b = b + 5;
6316+
QUERY PLAN
6317+
----------------------------------------------------------------------------------------
6318+
Update on public.parent_tbl
6319+
Foreign Update on public.foreign_tbl
6320+
Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
6321+
-> Foreign Scan on public.foreign_tbl
6322+
Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid
6323+
Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
6324+
(6 rows)
6325+
6326+
UPDATE rw_view SET b = b + 5; -- should fail
6327+
ERROR: new row violates check option for view "rw_view"
6328+
DETAIL: Failing row contains (20, 20).
6329+
EXPLAIN (VERBOSE, COSTS OFF)
6330+
UPDATE rw_view SET b = b + 15;
6331+
QUERY PLAN
6332+
----------------------------------------------------------------------------------------
6333+
Update on public.parent_tbl
6334+
Foreign Update on public.foreign_tbl
6335+
Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
6336+
-> Foreign Scan on public.foreign_tbl
6337+
Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid
6338+
Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
6339+
(6 rows)
6340+
6341+
UPDATE rw_view SET b = b + 15; -- ok
6342+
SELECT * FROM foreign_tbl;
6343+
a | b
6344+
----+----
6345+
20 | 30
6346+
(1 row)
6347+
6348+
DROP FOREIGN TABLE foreign_tbl CASCADE;
6349+
DROP TRIGGER row_before_insupd_trigger ON child_tbl;
6350+
DROP TABLE parent_tbl CASCADE;
6351+
NOTICE: drop cascades to view rw_view
6352+
DROP FUNCTION row_before_insupd_trigfunc;
62346353
-- ===================================================================
62356354
-- test serial columns (ie, sequence-based defaults)
62366355
-- ===================================================================

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 15 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1582,6 +1582,7 @@ postgresPlanForeignModify(PlannerInfo *root,
15821582
Relationrel;
15831583
StringInfoDatasql;
15841584
List*targetAttrs=NIL;
1585+
List*withCheckOptionList=NIL;
15851586
List*returningList=NIL;
15861587
List*retrieved_attrs=NIL;
15871588
booldoNothing= false;
@@ -1630,6 +1631,13 @@ postgresPlanForeignModify(PlannerInfo *root,
16301631
}
16311632
}
16321633

1634+
/*
1635+
* Extract the relevant WITH CHECK OPTION list if any.
1636+
*/
1637+
if (plan->withCheckOptionLists)
1638+
withCheckOptionList= (List*)list_nth(plan->withCheckOptionLists,
1639+
subplan_index);
1640+
16331641
/*
16341642
* Extract the relevant RETURNING list if any.
16351643
*/
@@ -1655,12 +1663,14 @@ postgresPlanForeignModify(PlannerInfo *root,
16551663
{
16561664
caseCMD_INSERT:
16571665
deparseInsertSql(&sql,rte,resultRelation,rel,
1658-
targetAttrs,doNothing,returningList,
1666+
targetAttrs,doNothing,
1667+
withCheckOptionList,returningList,
16591668
&retrieved_attrs);
16601669
break;
16611670
caseCMD_UPDATE:
16621671
deparseUpdateSql(&sql,rte,resultRelation,rel,
1663-
targetAttrs,returningList,
1672+
targetAttrs,
1673+
withCheckOptionList,returningList,
16641674
&retrieved_attrs);
16651675
break;
16661676
caseCMD_DELETE:
@@ -2046,7 +2056,9 @@ postgresBeginForeignInsert(ModifyTableState *mtstate,
20462056

20472057
/* Construct the SQL command string. */
20482058
deparseInsertSql(&sql,rte,resultRelation,rel,targetAttrs,doNothing,
2049-
resultRelInfo->ri_returningList,&retrieved_attrs);
2059+
resultRelInfo->ri_WithCheckOptions,
2060+
resultRelInfo->ri_returningList,
2061+
&retrieved_attrs);
20502062

20512063
/* Construct an execution state. */
20522064
fmstate=create_foreign_modify(mtstate->ps.state,

‎contrib/postgres_fdw/postgres_fdw.h

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -142,11 +142,13 @@ extern bool is_foreign_expr(PlannerInfo *root,
142142
Expr*expr);
143143
externvoiddeparseInsertSql(StringInfobuf,RangeTblEntry*rte,
144144
Indexrtindex,Relationrel,
145-
List*targetAttrs,booldoNothing,List*returningList,
145+
List*targetAttrs,booldoNothing,
146+
List*withCheckOptionList,List*returningList,
146147
List**retrieved_attrs);
147148
externvoiddeparseUpdateSql(StringInfobuf,RangeTblEntry*rte,
148149
Indexrtindex,Relationrel,
149-
List*targetAttrs,List*returningList,
150+
List*targetAttrs,
151+
List*withCheckOptionList,List*returningList,
150152
List**retrieved_attrs);
151153
externvoiddeparseDirectUpdateSql(StringInfobuf,PlannerInfo*root,
152154
Indexrtindex,Relationrel,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp