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

Commit89d349b

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Disable batch insert when BEFORE ROW INSERT triggers exist.
Previously, we allowed this, but such triggers might query the table toinsert into and act differently if the tuples that have already beenprocessed and prepared for insertion are not there, so disable it insuch cases.Back-patch to v14 where batch insert was added.Discussion:https://postgr.es/m/CAPmGK16_uPqsmgK0-LpLSUk54_BoK13bPrhxhfjSoSTVz414hA%40mail.gmail.com
1 parente452184 commit89d349b

File tree

3 files changed

+53
-4
lines changed

3 files changed

+53
-4
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9868,6 +9868,34 @@ SELECT COUNT(*) FROM ftable;
98689868
2
98699869
(1 row)
98709870

9871+
-- Disable batch inserting into foreign tables with BEFORE ROW INSERT triggers
9872+
-- even if the batch_size option is enabled.
9873+
ALTER FOREIGN TABLE ftable OPTIONS ( SET batch_size '10' );
9874+
CREATE TRIGGER trig_row_before BEFORE INSERT ON ftable
9875+
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
9876+
EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (3), (4);
9877+
QUERY PLAN
9878+
-------------------------------------------------------------
9879+
Insert on public.ftable
9880+
Remote SQL: INSERT INTO public.batch_table(x) VALUES ($1)
9881+
Batch Size: 1
9882+
-> Values Scan on "*VALUES*"
9883+
Output: "*VALUES*".column1
9884+
(5 rows)
9885+
9886+
INSERT INTO ftable VALUES (3), (4);
9887+
NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable
9888+
NOTICE: NEW: (3)
9889+
NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable
9890+
NOTICE: NEW: (4)
9891+
SELECT COUNT(*) FROM ftable;
9892+
count
9893+
-------
9894+
4
9895+
(1 row)
9896+
9897+
-- Clean up
9898+
DROP TRIGGER trig_row_before ON ftable;
98719899
DROP FOREIGN TABLE ftable;
98729900
DROP TABLE batch_table;
98739901
-- Use partitioning

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2012,8 +2012,8 @@ postgresExecForeignBatchInsert(EState *estate,
20122012
*Determine the maximum number of tuples that can be inserted in bulk
20132013
*
20142014
* Returns the batch size specified for server or table. When batching is not
2015-
* allowed (e.g. for tables with AFTER ROW triggers or with RETURNING clause),
2016-
* returns 1.
2015+
* allowed (e.g. for tables withBEFORE/AFTER ROW triggers or with RETURNING
2016+
*clause),returns 1.
20172017
*/
20182018
staticint
20192019
postgresGetForeignModifyBatchSize(ResultRelInfo*resultRelInfo)
@@ -2042,10 +2042,19 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo)
20422042
else
20432043
batch_size=get_batch_size_option(resultRelInfo->ri_RelationDesc);
20442044

2045-
/* Disable batching when we have to use RETURNING. */
2045+
/*
2046+
* Disable batching when we have to use RETURNING or there are any
2047+
* BEFORE/AFTER ROW INSERT triggers on the foreign table.
2048+
*
2049+
* When there are any BEFORE ROW INSERT triggers on the table, we can't
2050+
* support it, because such triggers might query the table we're inserting
2051+
* into and act differently if the tuples that have already been processed
2052+
* and prepared for insertion are not there.
2053+
*/
20462054
if (resultRelInfo->ri_projectReturning!=NULL||
20472055
(resultRelInfo->ri_TrigDesc&&
2048-
resultRelInfo->ri_TrigDesc->trig_insert_after_row))
2056+
(resultRelInfo->ri_TrigDesc->trig_insert_before_row||
2057+
resultRelInfo->ri_TrigDesc->trig_insert_after_row)))
20492058
return1;
20502059

20512060
/*

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3083,6 +3083,18 @@ CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batc
30833083
EXPLAIN (VERBOSE, COSTS OFF)INSERT INTO ftableVALUES (1), (2);
30843084
INSERT INTO ftableVALUES (1), (2);
30853085
SELECTCOUNT(*)FROM ftable;
3086+
3087+
-- Disable batch inserting into foreign tables with BEFORE ROW INSERT triggers
3088+
-- even if the batch_size option is enabled.
3089+
ALTER FOREIGN TABLE ftable OPTIONS (SET batch_size'10' );
3090+
CREATETRIGGERtrig_row_before BEFORE INSERTON ftable
3091+
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
3092+
EXPLAIN (VERBOSE, COSTS OFF)INSERT INTO ftableVALUES (3), (4);
3093+
INSERT INTO ftableVALUES (3), (4);
3094+
SELECTCOUNT(*)FROM ftable;
3095+
3096+
-- Clean up
3097+
DROPTRIGGER trig_row_beforeON ftable;
30863098
DROP FOREIGN TABLE ftable;
30873099
DROPTABLE batch_table;
30883100

0 commit comments

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