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

Commit82593b9

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 parentec0925c commit82593b9

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
@@ -6544,6 +6544,29 @@ SELECT * FROM foreign_tbl;
65446544
20 | 30
65456545
(1 row)
65466546

6547+
-- We don't allow batch insert when there are any WCO constraints
6548+
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
6549+
EXPLAIN (VERBOSE, COSTS OFF)
6550+
INSERT INTO rw_view VALUES (0, 15), (0, 5);
6551+
QUERY PLAN
6552+
--------------------------------------------------------------------------------
6553+
Insert on public.foreign_tbl
6554+
Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b
6555+
Batch Size: 1
6556+
-> Values Scan on "*VALUES*"
6557+
Output: "*VALUES*".column1, "*VALUES*".column2
6558+
(5 rows)
6559+
6560+
INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail
6561+
ERROR: new row violates check option for view "rw_view"
6562+
DETAIL: Failing row contains (10, 5).
6563+
SELECT * FROM foreign_tbl;
6564+
a | b
6565+
----+----
6566+
20 | 30
6567+
(1 row)
6568+
6569+
ALTER SERVER loopback OPTIONS (DROP batch_size);
65476570
DROP FOREIGN TABLE foreign_tbl CASCADE;
65486571
NOTICE: drop cascades to view rw_view
65496572
DROP TRIGGER row_before_insupd_trigger ON base_tbl;
@@ -6636,6 +6659,27 @@ SELECT * FROM foreign_tbl;
66366659
20 | 30
66376660
(1 row)
66386661

6662+
-- We don't allow batch insert when there are any WCO constraints
6663+
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
6664+
EXPLAIN (VERBOSE, COSTS OFF)
6665+
INSERT INTO rw_view VALUES (0, 15), (0, 5);
6666+
QUERY PLAN
6667+
--------------------------------------------------------
6668+
Insert on public.parent_tbl
6669+
-> Values Scan on "*VALUES*"
6670+
Output: "*VALUES*".column1, "*VALUES*".column2
6671+
(3 rows)
6672+
6673+
INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail
6674+
ERROR: new row violates check option for view "rw_view"
6675+
DETAIL: Failing row contains (10, 5).
6676+
SELECT * FROM foreign_tbl;
6677+
a | b
6678+
----+----
6679+
20 | 30
6680+
(1 row)
6681+
6682+
ALTER SERVER loopback OPTIONS (DROP batch_size);
66396683
DROP FOREIGN TABLE foreign_tbl CASCADE;
66406684
DROP TRIGGER row_before_insupd_trigger ON child_tbl;
66416685
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
@@ -1505,6 +1505,14 @@ UPDATE rw_view SET b = b + 15;
15051505
UPDATE rw_viewSET b= b+15;-- ok
15061506
SELECT*FROM foreign_tbl;
15071507

1508+
-- We don't allow batch insert when there are any WCO constraints
1509+
ALTER SERVER loopback OPTIONS (ADD batch_size'10');
1510+
EXPLAIN (VERBOSE, COSTS OFF)
1511+
INSERT INTO rw_viewVALUES (0,15), (0,5);
1512+
INSERT INTO rw_viewVALUES (0,15), (0,5);-- should fail
1513+
SELECT*FROM foreign_tbl;
1514+
ALTER SERVER loopback OPTIONS (DROP batch_size);
1515+
15081516
DROP FOREIGN TABLE foreign_tbl CASCADE;
15091517
DROPTRIGGER row_before_insupd_triggerON base_tbl;
15101518
DROPTABLE base_tbl;
@@ -1543,6 +1551,14 @@ UPDATE rw_view SET b = b + 15;
15431551
UPDATE rw_viewSET b= b+15;-- ok
15441552
SELECT*FROM foreign_tbl;
15451553

1554+
-- We don't allow batch insert when there are any WCO constraints
1555+
ALTER SERVER loopback OPTIONS (ADD batch_size'10');
1556+
EXPLAIN (VERBOSE, COSTS OFF)
1557+
INSERT INTO rw_viewVALUES (0,15), (0,5);
1558+
INSERT INTO rw_viewVALUES (0,15), (0,5);-- should fail
1559+
SELECT*FROM foreign_tbl;
1560+
ALTER SERVER loopback OPTIONS (DROP batch_size);
1561+
15461562
DROP FOREIGN TABLE foreign_tbl CASCADE;
15471563
DROPTRIGGER row_before_insupd_triggerON child_tbl;
15481564
DROPTABLE parent_tbl CASCADE;

0 commit comments

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