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

Commit1d49db2

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Disable batch insertion when there are WCO constraints.
When inserting a view referencing a foreign table that has WITH CHECKOPTION constraints, in single-insert mode postgres_fdw retrieves thedata that was actually inserted on the remote side so that the WITHCHECK OPTION constraints are enforced with the data locally, but inbatch-insert mode it cannot currently retrieve the data (except for therow first inserted through the view), resulting in enforcing the WITHCHECK OPTION constraints with the data passed from the core (except forthe first-inserted row), which led to incorrect results when insertinginto a view referencing a foreign table in which a remote BEFORE ROWINSERT trigger changes the rows inserted through the view so that theyviolate the view's WITH CHECK OPTION constraint. Also, the queryinserting into the view caused an assertion failure in assert-enabledbuilds.Fix these by disabling batch insertion when inserting into such a view.Back-patch to v14 where batch insertion was added.Discussion:https://postgr.es/m/CAPmGK17LpbTZs4m4a_6THP54UBeK9fHvX8aVVA%2BC6yEZDZwQcg%40mail.gmail.com
1 parente78fd90 commit1d49db2

File tree

3 files changed

+64
-2
lines changed

3 files changed

+64
-2
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6513,6 +6513,29 @@ SELECT * FROM foreign_tbl;
65136513
20 | 30
65146514
(1 row)
65156515

6516+
-- We don't allow batch insert when there are any WCO constraints
6517+
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
6518+
EXPLAIN (VERBOSE, COSTS OFF)
6519+
INSERT INTO rw_view VALUES (0, 15), (0, 5);
6520+
QUERY PLAN
6521+
--------------------------------------------------------------------------------
6522+
Insert on public.foreign_tbl
6523+
Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b
6524+
Batch Size: 1
6525+
-> Values Scan on "*VALUES*"
6526+
Output: "*VALUES*".column1, "*VALUES*".column2
6527+
(5 rows)
6528+
6529+
INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail
6530+
ERROR: new row violates check option for view "rw_view"
6531+
DETAIL: Failing row contains (10, 5).
6532+
SELECT * FROM foreign_tbl;
6533+
a | b
6534+
----+----
6535+
20 | 30
6536+
(1 row)
6537+
6538+
ALTER SERVER loopback OPTIONS (DROP batch_size);
65166539
DROP FOREIGN TABLE foreign_tbl CASCADE;
65176540
NOTICE: drop cascades to view rw_view
65186541
DROP TRIGGER row_before_insupd_trigger ON base_tbl;
@@ -6605,6 +6628,27 @@ SELECT * FROM foreign_tbl;
66056628
20 | 30
66066629
(1 row)
66076630

6631+
-- We don't allow batch insert when there are any WCO constraints
6632+
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
6633+
EXPLAIN (VERBOSE, COSTS OFF)
6634+
INSERT INTO rw_view VALUES (0, 15), (0, 5);
6635+
QUERY PLAN
6636+
--------------------------------------------------------
6637+
Insert on public.parent_tbl
6638+
-> Values Scan on "*VALUES*"
6639+
Output: "*VALUES*".column1, "*VALUES*".column2
6640+
(3 rows)
6641+
6642+
INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail
6643+
ERROR: new row violates check option for view "rw_view"
6644+
DETAIL: Failing row contains (10, 5).
6645+
SELECT * FROM foreign_tbl;
6646+
a | b
6647+
----+----
6648+
20 | 30
6649+
(1 row)
6650+
6651+
ALTER SERVER loopback OPTIONS (DROP batch_size);
66086652
DROP FOREIGN TABLE foreign_tbl CASCADE;
66096653
DROP TRIGGER row_before_insupd_trigger ON child_tbl;
66106654
DROP TABLE parent_tbl CASCADE;

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2043,15 +2043,17 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo)
20432043
batch_size=get_batch_size_option(resultRelInfo->ri_RelationDesc);
20442044

20452045
/*
2046-
* Disable batching when we have to use RETURNING or there are any
2047-
* BEFORE/AFTER ROW INSERT triggers on the foreign table.
2046+
* Disable batching when we have to use RETURNING, there are any
2047+
* BEFORE/AFTER ROW INSERT triggers on the foreign table, or there are any
2048+
* WITH CHECK OPTION constraints from parent views.
20482049
*
20492050
* When there are any BEFORE ROW INSERT triggers on the table, we can't
20502051
* support it, because such triggers might query the table we're inserting
20512052
* into and act differently if the tuples that have already been processed
20522053
* and prepared for insertion are not there.
20532054
*/
20542055
if (resultRelInfo->ri_projectReturning!=NULL||
2056+
resultRelInfo->ri_WithCheckOptions!=NIL||
20552057
(resultRelInfo->ri_TrigDesc&&
20562058
(resultRelInfo->ri_TrigDesc->trig_insert_before_row||
20572059
resultRelInfo->ri_TrigDesc->trig_insert_after_row)))

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1493,6 +1493,14 @@ UPDATE rw_view SET b = b + 15;
14931493
UPDATE rw_viewSET b= b+15;-- ok
14941494
SELECT*FROM foreign_tbl;
14951495

1496+
-- We don't allow batch insert when there are any WCO constraints
1497+
ALTER SERVER loopback OPTIONS (ADD batch_size'10');
1498+
EXPLAIN (VERBOSE, COSTS OFF)
1499+
INSERT INTO rw_viewVALUES (0,15), (0,5);
1500+
INSERT INTO rw_viewVALUES (0,15), (0,5);-- should fail
1501+
SELECT*FROM foreign_tbl;
1502+
ALTER SERVER loopback OPTIONS (DROP batch_size);
1503+
14961504
DROP FOREIGN TABLE foreign_tbl CASCADE;
14971505
DROPTRIGGER row_before_insupd_triggerON base_tbl;
14981506
DROPTABLE base_tbl;
@@ -1531,6 +1539,14 @@ UPDATE rw_view SET b = b + 15;
15311539
UPDATE rw_viewSET b= b+15;-- ok
15321540
SELECT*FROM foreign_tbl;
15331541

1542+
-- We don't allow batch insert when there are any WCO constraints
1543+
ALTER SERVER loopback OPTIONS (ADD batch_size'10');
1544+
EXPLAIN (VERBOSE, COSTS OFF)
1545+
INSERT INTO rw_viewVALUES (0,15), (0,5);
1546+
INSERT INTO rw_viewVALUES (0,15), (0,5);-- should fail
1547+
SELECT*FROM foreign_tbl;
1548+
ALTER SERVER loopback OPTIONS (DROP batch_size);
1549+
15341550
DROP FOREIGN TABLE foreign_tbl CASCADE;
15351551
DROPTRIGGER row_before_insupd_triggerON child_tbl;
15361552
DROPTABLE parent_tbl CASCADE;

0 commit comments

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