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

Commit4a9bc2e

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 parent731d514 commit4a9bc2e

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
@@ -6373,6 +6373,29 @@ SELECT * FROM foreign_tbl;
63736373
20 | 30
63746374
(1 row)
63756375

6376+
-- We don't allow batch insert when there are any WCO constraints
6377+
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
6378+
EXPLAIN (VERBOSE, COSTS OFF)
6379+
INSERT INTO rw_view VALUES (0, 15), (0, 5);
6380+
QUERY PLAN
6381+
--------------------------------------------------------------------------------
6382+
Insert on public.foreign_tbl
6383+
Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b
6384+
Batch Size: 1
6385+
-> Values Scan on "*VALUES*"
6386+
Output: "*VALUES*".column1, "*VALUES*".column2
6387+
(5 rows)
6388+
6389+
INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail
6390+
ERROR: new row violates check option for view "rw_view"
6391+
DETAIL: Failing row contains (10, 5).
6392+
SELECT * FROM foreign_tbl;
6393+
a | b
6394+
----+----
6395+
20 | 30
6396+
(1 row)
6397+
6398+
ALTER SERVER loopback OPTIONS (DROP batch_size);
63766399
DROP FOREIGN TABLE foreign_tbl CASCADE;
63776400
NOTICE: drop cascades to view rw_view
63786401
DROP TRIGGER row_before_insupd_trigger ON base_tbl;
@@ -6465,6 +6488,27 @@ SELECT * FROM foreign_tbl;
64656488
20 | 30
64666489
(1 row)
64676490

6491+
-- We don't allow batch insert when there are any WCO constraints
6492+
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
6493+
EXPLAIN (VERBOSE, COSTS OFF)
6494+
INSERT INTO rw_view VALUES (0, 15), (0, 5);
6495+
QUERY PLAN
6496+
--------------------------------------------------------
6497+
Insert on public.parent_tbl
6498+
-> Values Scan on "*VALUES*"
6499+
Output: "*VALUES*".column1, "*VALUES*".column2
6500+
(3 rows)
6501+
6502+
INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail
6503+
ERROR: new row violates check option for view "rw_view"
6504+
DETAIL: Failing row contains (10, 5).
6505+
SELECT * FROM foreign_tbl;
6506+
a | b
6507+
----+----
6508+
20 | 30
6509+
(1 row)
6510+
6511+
ALTER SERVER loopback OPTIONS (DROP batch_size);
64686512
DROP FOREIGN TABLE foreign_tbl CASCADE;
64696513
DROP TRIGGER row_before_insupd_trigger ON child_tbl;
64706514
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
@@ -1441,6 +1441,14 @@ UPDATE rw_view SET b = b + 15;
14411441
UPDATE rw_viewSET b= b+15;-- ok
14421442
SELECT*FROM foreign_tbl;
14431443

1444+
-- We don't allow batch insert when there are any WCO constraints
1445+
ALTER SERVER loopback OPTIONS (ADD batch_size'10');
1446+
EXPLAIN (VERBOSE, COSTS OFF)
1447+
INSERT INTO rw_viewVALUES (0,15), (0,5);
1448+
INSERT INTO rw_viewVALUES (0,15), (0,5);-- should fail
1449+
SELECT*FROM foreign_tbl;
1450+
ALTER SERVER loopback OPTIONS (DROP batch_size);
1451+
14441452
DROP FOREIGN TABLE foreign_tbl CASCADE;
14451453
DROPTRIGGER row_before_insupd_triggerON base_tbl;
14461454
DROPTABLE base_tbl;
@@ -1479,6 +1487,14 @@ UPDATE rw_view SET b = b + 15;
14791487
UPDATE rw_viewSET b= b+15;-- ok
14801488
SELECT*FROM foreign_tbl;
14811489

1490+
-- We don't allow batch insert when there are any WCO constraints
1491+
ALTER SERVER loopback OPTIONS (ADD batch_size'10');
1492+
EXPLAIN (VERBOSE, COSTS OFF)
1493+
INSERT INTO rw_viewVALUES (0,15), (0,5);
1494+
INSERT INTO rw_viewVALUES (0,15), (0,5);-- should fail
1495+
SELECT*FROM foreign_tbl;
1496+
ALTER SERVER loopback OPTIONS (DROP batch_size);
1497+
14821498
DROP FOREIGN TABLE foreign_tbl CASCADE;
14831499
DROPTRIGGER row_before_insupd_triggerON child_tbl;
14841500
DROPTABLE parent_tbl CASCADE;

0 commit comments

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