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

Commit4eea220

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 parentba6af6a commit4eea220

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
@@ -10008,6 +10008,34 @@ SELECT COUNT(*) FROM ftable;
1000810008
2
1000910009
(1 row)
1001010010

10011+
-- Disable batch inserting into foreign tables with BEFORE ROW INSERT triggers
10012+
-- even if the batch_size option is enabled.
10013+
ALTER FOREIGN TABLE ftable OPTIONS ( SET batch_size '10' );
10014+
CREATE TRIGGER trig_row_before BEFORE INSERT ON ftable
10015+
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
10016+
EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (3), (4);
10017+
QUERY PLAN
10018+
-------------------------------------------------------------
10019+
Insert on public.ftable
10020+
Remote SQL: INSERT INTO public.batch_table(x) VALUES ($1)
10021+
Batch Size: 1
10022+
-> Values Scan on "*VALUES*"
10023+
Output: "*VALUES*".column1
10024+
(5 rows)
10025+
10026+
INSERT INTO ftable VALUES (3), (4);
10027+
NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable
10028+
NOTICE: NEW: (3)
10029+
NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable
10030+
NOTICE: NEW: (4)
10031+
SELECT COUNT(*) FROM ftable;
10032+
count
10033+
-------
10034+
4
10035+
(1 row)
10036+
10037+
-- Clean up
10038+
DROP TRIGGER trig_row_before ON ftable;
1001110039
DROP FOREIGN TABLE ftable;
1001210040
DROP TABLE batch_table;
1001310041
-- 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
@@ -3135,6 +3135,18 @@ CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batc
31353135
EXPLAIN (VERBOSE, COSTS OFF)INSERT INTO ftableVALUES (1), (2);
31363136
INSERT INTO ftableVALUES (1), (2);
31373137
SELECTCOUNT(*)FROM ftable;
3138+
3139+
-- Disable batch inserting into foreign tables with BEFORE ROW INSERT triggers
3140+
-- even if the batch_size option is enabled.
3141+
ALTER FOREIGN TABLE ftable OPTIONS (SET batch_size'10' );
3142+
CREATETRIGGERtrig_row_before BEFORE INSERTON ftable
3143+
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
3144+
EXPLAIN (VERBOSE, COSTS OFF)INSERT INTO ftableVALUES (3), (4);
3145+
INSERT INTO ftableVALUES (3), (4);
3146+
SELECTCOUNT(*)FROM ftable;
3147+
3148+
-- Clean up
3149+
DROPTRIGGER trig_row_beforeON ftable;
31383150
DROP FOREIGN TABLE ftable;
31393151
DROPTABLE batch_table;
31403152

0 commit comments

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