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

Commite522452

Browse files
author
Etsuro Fujita
committed
Fix handling of pending inserts in nodeModifyTable.c.
Commitb663a41, which allowed FDWs to INSERT rows in bulk, added tonodeModifyTable.c code to flush pending inserts to the foreign-tableresult relation(s) before completing processing of the ModifyTable node,but the code failed to take into account the case where the INSERT queryhas modifying CTEs, leading to incorrect results.Also, that commit failed to flush pending inserts before firing BEFOREROW triggers so that rows are visible to such triggers.In that commit we scanned through EState'ses_tuple_routing_result_relations or es_opened_result_relations list tofind the foreign-table result relations to which pending inserts areflushed, but that would be inefficient in some cases. So to fix, 1) adda List member to EState to record the insert-pending result relations,and 2) modify nodeModifyTable.c so that it adds the foreign-table resultrelation to the list in ExecInsert() if appropriate, and flushes pendinginserts properly using the list where needed.While here, fix a copy-and-pasteo in a comment in ExecBatchInsert(),which was added by that commit.Back-patch to v14 where that commit appeared.Discussion:https://postgr.es/m/CAPmGK16qutyCmyJJzgQOhfBq%3DNoGDqTB6O0QBZTihrbqre%2BoxA%40mail.gmail.com
1 parent9f2cc1a commite522452

File tree

7 files changed

+298
-21
lines changed

7 files changed

+298
-21
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10168,7 +10168,130 @@ SELECT * FROM batch_table ORDER BY x;
1016810168
50 | test50 | test50
1016910169
(50 rows)
1017010170

10171+
-- Clean up
10172+
DROP TABLE batch_table;
10173+
DROP TABLE batch_table_p0;
10174+
DROP TABLE batch_table_p1;
1017110175
ALTER SERVER loopback OPTIONS (DROP batch_size);
10176+
-- Test that pending inserts are handled properly when needed
10177+
CREATE TABLE batch_table (a text, b int);
10178+
CREATE FOREIGN TABLE ftable (a text, b int)
10179+
SERVER loopback
10180+
OPTIONS (table_name 'batch_table', batch_size '2');
10181+
CREATE TABLE ltable (a text, b int);
10182+
CREATE FUNCTION ftable_rowcount_trigf() RETURNS trigger LANGUAGE plpgsql AS
10183+
$$
10184+
begin
10185+
raise notice '%: there are % rows in ftable',
10186+
TG_NAME, (SELECT count(*) FROM ftable);
10187+
if TG_OP = 'DELETE' then
10188+
return OLD;
10189+
else
10190+
return NEW;
10191+
end if;
10192+
end;
10193+
$$;
10194+
CREATE TRIGGER ftable_rowcount_trigger
10195+
BEFORE INSERT OR UPDATE OR DELETE ON ltable
10196+
FOR EACH ROW EXECUTE PROCEDURE ftable_rowcount_trigf();
10197+
WITH t AS (
10198+
INSERT INTO ltable VALUES ('AAA', 42), ('BBB', 42) RETURNING *
10199+
)
10200+
INSERT INTO ftable SELECT * FROM t;
10201+
NOTICE: ftable_rowcount_trigger: there are 0 rows in ftable
10202+
NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable
10203+
SELECT * FROM ltable;
10204+
a | b
10205+
-----+----
10206+
AAA | 42
10207+
BBB | 42
10208+
(2 rows)
10209+
10210+
SELECT * FROM ftable;
10211+
a | b
10212+
-----+----
10213+
AAA | 42
10214+
BBB | 42
10215+
(2 rows)
10216+
10217+
DELETE FROM ftable;
10218+
WITH t AS (
10219+
UPDATE ltable SET b = b + 100 RETURNING *
10220+
)
10221+
INSERT INTO ftable SELECT * FROM t;
10222+
NOTICE: ftable_rowcount_trigger: there are 0 rows in ftable
10223+
NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable
10224+
SELECT * FROM ltable;
10225+
a | b
10226+
-----+-----
10227+
AAA | 142
10228+
BBB | 142
10229+
(2 rows)
10230+
10231+
SELECT * FROM ftable;
10232+
a | b
10233+
-----+-----
10234+
AAA | 142
10235+
BBB | 142
10236+
(2 rows)
10237+
10238+
DELETE FROM ftable;
10239+
WITH t AS (
10240+
DELETE FROM ltable RETURNING *
10241+
)
10242+
INSERT INTO ftable SELECT * FROM t;
10243+
NOTICE: ftable_rowcount_trigger: there are 0 rows in ftable
10244+
NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable
10245+
SELECT * FROM ltable;
10246+
a | b
10247+
---+---
10248+
(0 rows)
10249+
10250+
SELECT * FROM ftable;
10251+
a | b
10252+
-----+-----
10253+
AAA | 142
10254+
BBB | 142
10255+
(2 rows)
10256+
10257+
DELETE FROM ftable;
10258+
-- Clean up
10259+
DROP FOREIGN TABLE ftable;
10260+
DROP TABLE batch_table;
10261+
DROP TRIGGER ftable_rowcount_trigger ON ltable;
10262+
DROP TABLE ltable;
10263+
CREATE TABLE parent (a text, b int) PARTITION BY LIST (a);
10264+
CREATE TABLE batch_table (a text, b int);
10265+
CREATE FOREIGN TABLE ftable
10266+
PARTITION OF parent
10267+
FOR VALUES IN ('AAA')
10268+
SERVER loopback
10269+
OPTIONS (table_name 'batch_table', batch_size '2');
10270+
CREATE TABLE ltable
10271+
PARTITION OF parent
10272+
FOR VALUES IN ('BBB');
10273+
CREATE TRIGGER ftable_rowcount_trigger
10274+
BEFORE INSERT ON ltable
10275+
FOR EACH ROW EXECUTE PROCEDURE ftable_rowcount_trigf();
10276+
INSERT INTO parent VALUES ('AAA', 42), ('BBB', 42), ('AAA', 42), ('BBB', 42);
10277+
NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable
10278+
NOTICE: ftable_rowcount_trigger: there are 2 rows in ftable
10279+
SELECT tableoid::regclass, * FROM parent;
10280+
tableoid | a | b
10281+
----------+-----+----
10282+
ftable | AAA | 42
10283+
ftable | AAA | 42
10284+
ltable | BBB | 42
10285+
ltable | BBB | 42
10286+
(4 rows)
10287+
10288+
-- Clean up
10289+
DROP FOREIGN TABLE ftable;
10290+
DROP TABLE batch_table;
10291+
DROP TRIGGER ftable_rowcount_trigger ON ltable;
10292+
DROP TABLE ltable;
10293+
DROP TABLE parent;
10294+
DROP FUNCTION ftable_rowcount_trigf;
1017210295
-- ===================================================================
1017310296
-- test asynchronous execution
1017410297
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3205,8 +3205,94 @@ INSERT INTO batch_table SELECT i, 'test'||i, 'test'|| i FROM generate_series(1,
32053205
SELECTCOUNT(*)FROM batch_table;
32063206
SELECT*FROM batch_tableORDER BY x;
32073207

3208+
-- Clean up
3209+
DROPTABLE batch_table;
3210+
DROPTABLE batch_table_p0;
3211+
DROPTABLE batch_table_p1;
3212+
32083213
ALTER SERVER loopback OPTIONS (DROP batch_size);
32093214

3215+
-- Test that pending inserts are handled properly when needed
3216+
CREATETABLEbatch_table (atext, bint);
3217+
CREATE FOREIGN TABLE ftable (atext, bint)
3218+
SERVER loopback
3219+
OPTIONS (table_name'batch_table', batch_size'2');
3220+
CREATETABLEltable (atext, bint);
3221+
CREATEFUNCTIONftable_rowcount_trigf() RETURNS trigger LANGUAGE plpgsqlAS
3222+
$$
3223+
begin
3224+
raise notice'%: there are % rows in ftable',
3225+
TG_NAME, (SELECTcount(*)FROM ftable);
3226+
if TG_OP='DELETE' then
3227+
return OLD;
3228+
else
3229+
return NEW;
3230+
end if;
3231+
end;
3232+
$$;
3233+
CREATETRIGGERftable_rowcount_trigger
3234+
BEFORE INSERTORUPDATEORDELETEON ltable
3235+
FOR EACH ROW EXECUTE PROCEDURE ftable_rowcount_trigf();
3236+
3237+
WITH tAS (
3238+
INSERT INTO ltableVALUES ('AAA',42), ('BBB',42) RETURNING*
3239+
)
3240+
INSERT INTO ftableSELECT*FROM t;
3241+
3242+
SELECT*FROM ltable;
3243+
SELECT*FROM ftable;
3244+
DELETEFROM ftable;
3245+
3246+
WITH tAS (
3247+
UPDATE ltableSET b= b+100 RETURNING*
3248+
)
3249+
INSERT INTO ftableSELECT*FROM t;
3250+
3251+
SELECT*FROM ltable;
3252+
SELECT*FROM ftable;
3253+
DELETEFROM ftable;
3254+
3255+
WITH tAS (
3256+
DELETEFROM ltable RETURNING*
3257+
)
3258+
INSERT INTO ftableSELECT*FROM t;
3259+
3260+
SELECT*FROM ltable;
3261+
SELECT*FROM ftable;
3262+
DELETEFROM ftable;
3263+
3264+
-- Clean up
3265+
DROP FOREIGN TABLE ftable;
3266+
DROPTABLE batch_table;
3267+
DROPTRIGGER ftable_rowcount_triggerON ltable;
3268+
DROPTABLE ltable;
3269+
3270+
CREATETABLEparent (atext, bint) PARTITION BY LIST (a);
3271+
CREATETABLEbatch_table (atext, bint);
3272+
CREATE FOREIGN TABLE ftable
3273+
PARTITION OF parent
3274+
FORVALUESIN ('AAA')
3275+
SERVER loopback
3276+
OPTIONS (table_name'batch_table', batch_size'2');
3277+
CREATETABLEltable
3278+
PARTITION OF parent
3279+
FORVALUESIN ('BBB');
3280+
CREATETRIGGERftable_rowcount_trigger
3281+
BEFORE INSERTON ltable
3282+
FOR EACH ROW EXECUTE PROCEDURE ftable_rowcount_trigf();
3283+
3284+
INSERT INTO parentVALUES ('AAA',42), ('BBB',42), ('AAA',42), ('BBB',42);
3285+
3286+
SELECT tableoid::regclass,*FROM parent;
3287+
3288+
-- Clean up
3289+
DROP FOREIGN TABLE ftable;
3290+
DROPTABLE batch_table;
3291+
DROPTRIGGER ftable_rowcount_triggerON ltable;
3292+
DROPTABLE ltable;
3293+
DROPTABLE parent;
3294+
DROPFUNCTION ftable_rowcount_trigf;
3295+
32103296
-- ===================================================================
32113297
-- test asynchronous execution
32123298
-- ===================================================================

‎src/backend/executor/execMain.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1257,6 +1257,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
12571257
resultRelInfo->ri_ChildToRootMap=NULL;
12581258
resultRelInfo->ri_ChildToRootMapValid= false;
12591259
resultRelInfo->ri_CopyMultiInsertBuffer=NULL;
1260+
resultRelInfo->ri_ModifyTableState=NULL;
12601261
}
12611262

12621263
/*

‎src/backend/executor/execPartition.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -934,6 +934,13 @@ ExecInitRoutingInfo(ModifyTableState *mtstate,
934934

935935
Assert(partRelInfo->ri_BatchSize >=1);
936936

937+
/*
938+
* If doing batch insert, setup back-link so we can easily find the
939+
* mtstate again.
940+
*/
941+
if (partRelInfo->ri_BatchSize>1)
942+
partRelInfo->ri_ModifyTableState=mtstate;
943+
937944
partRelInfo->ri_CopyMultiInsertBuffer=NULL;
938945

939946
/*

‎src/backend/executor/execUtils.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -127,6 +127,7 @@ CreateExecutorState(void)
127127
estate->es_result_relations=NULL;
128128
estate->es_opened_result_relations=NIL;
129129
estate->es_tuple_routing_result_relations=NIL;
130+
estate->es_insert_pending_result_relations=NIL;
130131
estate->es_trig_target_relations=NIL;
131132

132133
estate->es_param_list_info=NULL;

0 commit comments

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