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

Commit9cca445

Browse files
author
Etsuro Fujita
committed
Disallow collecting transition tuples from child foreign tables.
Commit9e6104c disallowed transition tables on foreign tables, butfailed to account for cases where a foreign table is a child table of apartitioned/inherited table on which transition tables exist, leading toincorrect transition tuples collected from such foreign tables forqueries on the parent table triggering transition capture. Thisoccurred not only for inherited UPDATE/DELETE but for partitioned INSERTlater supported by commit3d956d9, which should have handled it atleast for the INSERT case, but didn't.To fix, modify ExecAR*Triggers to throw an error if the given relationis a foreign table requesting transition capture. Also, this commitfixes make_modifytable so that in case of an inherited UPDATE/DELETEtriggering transition capture, FDWs choose normal operations to modifychild foreign tables, not DirectModify; which is needed because theywould otherwise skip the calls to ExecAR*Triggers at execution, causingunexpected behavior.Author: Etsuro Fujita <etsuro.fujita@gmail.com>Reviewed-by: Amit Langote <amitlangote09@gmail.com>Discussion:https://postgr.es/m/CAPmGK14QJYikKzBDCe3jMbpGENnQ7popFmbEgm-XTNuk55oyHg%40mail.gmail.comBackpatch-through: 13
1 parentf770006 commit9cca445

File tree

6 files changed

+291
-2
lines changed

6 files changed

+291
-2
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out‎

Lines changed: 113 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7723,6 +7723,119 @@ DELETE FROM rem1; -- can't be pushed down
77237723
(5 rows)
77247724

77257725
DROP TRIGGER trig_row_after_delete ON rem1;
7726+
-- We are allowed to create transition-table triggers on both kinds of
7727+
-- inheritance even if they contain foreign tables as children, but currently
7728+
-- collecting transition tuples from such foreign tables is not supported.
7729+
CREATE TABLE local_tbl (a text, b int);
7730+
CREATE FOREIGN TABLE foreign_tbl (a text, b int)
7731+
SERVER loopback OPTIONS (table_name 'local_tbl');
7732+
INSERT INTO foreign_tbl VALUES ('AAA', 42);
7733+
-- Test case for partition hierarchy
7734+
CREATE TABLE parent_tbl (a text, b int) PARTITION BY LIST (a);
7735+
ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES IN ('AAA');
7736+
CREATE TRIGGER parent_tbl_insert_trig
7737+
AFTER INSERT ON parent_tbl REFERENCING NEW TABLE AS new_table
7738+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
7739+
CREATE TRIGGER parent_tbl_update_trig
7740+
AFTER UPDATE ON parent_tbl REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
7741+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
7742+
CREATE TRIGGER parent_tbl_delete_trig
7743+
AFTER DELETE ON parent_tbl REFERENCING OLD TABLE AS old_table
7744+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
7745+
INSERT INTO parent_tbl VALUES ('AAA', 42);
7746+
ERROR: cannot collect transition tuples from child foreign tables
7747+
COPY parent_tbl (a, b) FROM stdin;
7748+
ERROR: cannot collect transition tuples from child foreign tables
7749+
CONTEXT: COPY parent_tbl, line 1: "AAA42"
7750+
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
7751+
INSERT INTO parent_tbl VALUES ('AAA', 42);
7752+
ERROR: cannot collect transition tuples from child foreign tables
7753+
COPY parent_tbl (a, b) FROM stdin;
7754+
ERROR: cannot collect transition tuples from child foreign tables
7755+
CONTEXT: COPY parent_tbl, line 1: "AAA42"
7756+
ALTER SERVER loopback OPTIONS (DROP batch_size);
7757+
EXPLAIN (VERBOSE, COSTS OFF)
7758+
UPDATE parent_tbl SET b = b + 1;
7759+
QUERY PLAN
7760+
------------------------------------------------------------------------------------------------
7761+
Update on public.parent_tbl
7762+
Foreign Update on public.foreign_tbl parent_tbl_1
7763+
Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1
7764+
-> Foreign Scan on public.foreign_tbl parent_tbl_1
7765+
Output: (parent_tbl_1.b + 1), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.*
7766+
Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE
7767+
(6 rows)
7768+
7769+
UPDATE parent_tbl SET b = b + 1;
7770+
ERROR: cannot collect transition tuples from child foreign tables
7771+
EXPLAIN (VERBOSE, COSTS OFF)
7772+
DELETE FROM parent_tbl;
7773+
QUERY PLAN
7774+
------------------------------------------------------------------
7775+
Delete on public.parent_tbl
7776+
Foreign Delete on public.foreign_tbl parent_tbl_1
7777+
Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1
7778+
-> Foreign Scan on public.foreign_tbl parent_tbl_1
7779+
Output: parent_tbl_1.tableoid, parent_tbl_1.ctid
7780+
Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE
7781+
(6 rows)
7782+
7783+
DELETE FROM parent_tbl;
7784+
ERROR: cannot collect transition tuples from child foreign tables
7785+
ALTER TABLE parent_tbl DETACH PARTITION foreign_tbl;
7786+
DROP TABLE parent_tbl;
7787+
-- Test case for non-partition hierarchy
7788+
CREATE TABLE parent_tbl (a text, b int);
7789+
ALTER FOREIGN TABLE foreign_tbl INHERIT parent_tbl;
7790+
CREATE TRIGGER parent_tbl_update_trig
7791+
AFTER UPDATE ON parent_tbl REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
7792+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
7793+
CREATE TRIGGER parent_tbl_delete_trig
7794+
AFTER DELETE ON parent_tbl REFERENCING OLD TABLE AS old_table
7795+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
7796+
EXPLAIN (VERBOSE, COSTS OFF)
7797+
UPDATE parent_tbl SET b = b + 1;
7798+
QUERY PLAN
7799+
------------------------------------------------------------------------------------------------------
7800+
Update on public.parent_tbl
7801+
Update on public.parent_tbl parent_tbl_1
7802+
Foreign Update on public.foreign_tbl parent_tbl_2
7803+
Remote SQL: UPDATE public.local_tbl SET b = $2 WHERE ctid = $1
7804+
-> Result
7805+
Output: (parent_tbl.b + 1), parent_tbl.tableoid, parent_tbl.ctid, (NULL::record)
7806+
-> Append
7807+
-> Seq Scan on public.parent_tbl parent_tbl_1
7808+
Output: parent_tbl_1.b, parent_tbl_1.tableoid, parent_tbl_1.ctid, NULL::record
7809+
-> Foreign Scan on public.foreign_tbl parent_tbl_2
7810+
Output: parent_tbl_2.b, parent_tbl_2.tableoid, parent_tbl_2.ctid, parent_tbl_2.*
7811+
Remote SQL: SELECT a, b, ctid FROM public.local_tbl FOR UPDATE
7812+
(12 rows)
7813+
7814+
UPDATE parent_tbl SET b = b + 1;
7815+
ERROR: cannot collect transition tuples from child foreign tables
7816+
EXPLAIN (VERBOSE, COSTS OFF)
7817+
DELETE FROM parent_tbl;
7818+
QUERY PLAN
7819+
------------------------------------------------------------------------
7820+
Delete on public.parent_tbl
7821+
Delete on public.parent_tbl parent_tbl_1
7822+
Foreign Delete on public.foreign_tbl parent_tbl_2
7823+
Remote SQL: DELETE FROM public.local_tbl WHERE ctid = $1
7824+
-> Append
7825+
-> Seq Scan on public.parent_tbl parent_tbl_1
7826+
Output: parent_tbl_1.tableoid, parent_tbl_1.ctid
7827+
-> Foreign Scan on public.foreign_tbl parent_tbl_2
7828+
Output: parent_tbl_2.tableoid, parent_tbl_2.ctid
7829+
Remote SQL: SELECT ctid FROM public.local_tbl FOR UPDATE
7830+
(10 rows)
7831+
7832+
DELETE FROM parent_tbl;
7833+
ERROR: cannot collect transition tuples from child foreign tables
7834+
ALTER FOREIGN TABLE foreign_tbl NO INHERIT parent_tbl;
7835+
DROP TABLE parent_tbl;
7836+
-- Cleanup
7837+
DROP FOREIGN TABLE foreign_tbl;
7838+
DROP TABLE local_tbl;
77267839
-- ===================================================================
77277840
-- test inheritance features
77287841
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql‎

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2088,6 +2088,84 @@ EXPLAIN (verbose, costs off)
20882088
DELETEFROM rem1;-- can't be pushed down
20892089
DROPTRIGGER trig_row_after_deleteON rem1;
20902090

2091+
2092+
-- We are allowed to create transition-table triggers on both kinds of
2093+
-- inheritance even if they contain foreign tables as children, but currently
2094+
-- collecting transition tuples from such foreign tables is not supported.
2095+
2096+
CREATETABLElocal_tbl (atext, bint);
2097+
CREATE FOREIGN TABLE foreign_tbl (atext, bint)
2098+
SERVER loopback OPTIONS (table_name'local_tbl');
2099+
2100+
INSERT INTO foreign_tblVALUES ('AAA',42);
2101+
2102+
-- Test case for partition hierarchy
2103+
CREATETABLEparent_tbl (atext, bint) PARTITION BY LIST (a);
2104+
ALTERTABLE parent_tbl ATTACH PARTITION foreign_tbl FORVALUESIN ('AAA');
2105+
2106+
CREATETRIGGERparent_tbl_insert_trig
2107+
AFTER INSERTON parent_tbl REFERENCING NEW TABLEAS new_table
2108+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
2109+
CREATETRIGGERparent_tbl_update_trig
2110+
AFTERUPDATEON parent_tbl REFERENCING OLD TABLEAS old_table NEW TABLEAS new_table
2111+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
2112+
CREATETRIGGERparent_tbl_delete_trig
2113+
AFTERDELETEON parent_tbl REFERENCING OLD TABLEAS old_table
2114+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
2115+
2116+
INSERT INTO parent_tblVALUES ('AAA',42);
2117+
2118+
COPY parent_tbl (a, b)FROM stdin;
2119+
AAA42
2120+
\.
2121+
2122+
ALTER SERVER loopback OPTIONS (ADD batch_size'10');
2123+
2124+
INSERT INTO parent_tblVALUES ('AAA',42);
2125+
2126+
COPY parent_tbl (a, b)FROM stdin;
2127+
AAA42
2128+
\.
2129+
2130+
ALTER SERVER loopback OPTIONS (DROP batch_size);
2131+
2132+
EXPLAIN (VERBOSE, COSTS OFF)
2133+
UPDATE parent_tblSET b= b+1;
2134+
UPDATE parent_tblSET b= b+1;
2135+
2136+
EXPLAIN (VERBOSE, COSTS OFF)
2137+
DELETEFROM parent_tbl;
2138+
DELETEFROM parent_tbl;
2139+
2140+
ALTERTABLE parent_tbl DETACH PARTITION foreign_tbl;
2141+
DROPTABLE parent_tbl;
2142+
2143+
-- Test case for non-partition hierarchy
2144+
CREATETABLEparent_tbl (atext, bint);
2145+
ALTER FOREIGN TABLE foreign_tbl INHERIT parent_tbl;
2146+
2147+
CREATETRIGGERparent_tbl_update_trig
2148+
AFTERUPDATEON parent_tbl REFERENCING OLD TABLEAS old_table NEW TABLEAS new_table
2149+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
2150+
CREATETRIGGERparent_tbl_delete_trig
2151+
AFTERDELETEON parent_tbl REFERENCING OLD TABLEAS old_table
2152+
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
2153+
2154+
EXPLAIN (VERBOSE, COSTS OFF)
2155+
UPDATE parent_tblSET b= b+1;
2156+
UPDATE parent_tblSET b= b+1;
2157+
2158+
EXPLAIN (VERBOSE, COSTS OFF)
2159+
DELETEFROM parent_tbl;
2160+
DELETEFROM parent_tbl;
2161+
2162+
ALTER FOREIGN TABLE foreign_tbl NO INHERIT parent_tbl;
2163+
DROPTABLE parent_tbl;
2164+
2165+
-- Cleanup
2166+
DROP FOREIGN TABLE foreign_tbl;
2167+
DROPTABLE local_tbl;
2168+
20912169
-- ===================================================================
20922170
-- test inheritance features
20932171
-- ===================================================================

‎src/backend/commands/trigger.c‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2543,6 +2543,15 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
25432543
{
25442544
TriggerDesc*trigdesc=relinfo->ri_TrigDesc;
25452545

2546+
if (relinfo->ri_FdwRoutine&&transition_capture&&
2547+
transition_capture->tcs_insert_new_table)
2548+
{
2549+
Assert(relinfo->ri_RootResultRelInfo);
2550+
ereport(ERROR,
2551+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2552+
errmsg("cannot collect transition tuples from child foreign tables")));
2553+
}
2554+
25462555
if ((trigdesc&&trigdesc->trig_insert_after_row)||
25472556
(transition_capture&&transition_capture->tcs_insert_new_table))
25482557
AfterTriggerSaveEvent(estate,relinfo,NULL,NULL,
@@ -2786,6 +2795,15 @@ ExecARDeleteTriggers(EState *estate,
27862795
{
27872796
TriggerDesc*trigdesc=relinfo->ri_TrigDesc;
27882797

2798+
if (relinfo->ri_FdwRoutine&&transition_capture&&
2799+
transition_capture->tcs_delete_old_table)
2800+
{
2801+
Assert(relinfo->ri_RootResultRelInfo);
2802+
ereport(ERROR,
2803+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2804+
errmsg("cannot collect transition tuples from child foreign tables")));
2805+
}
2806+
27892807
if ((trigdesc&&trigdesc->trig_delete_after_row)||
27902808
(transition_capture&&transition_capture->tcs_delete_old_table))
27912809
{
@@ -3112,6 +3130,16 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
31123130
{
31133131
TriggerDesc*trigdesc=relinfo->ri_TrigDesc;
31143132

3133+
if (relinfo->ri_FdwRoutine&&transition_capture&&
3134+
(transition_capture->tcs_update_old_table||
3135+
transition_capture->tcs_update_new_table))
3136+
{
3137+
Assert(relinfo->ri_RootResultRelInfo);
3138+
ereport(ERROR,
3139+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3140+
errmsg("cannot collect transition tuples from child foreign tables")));
3141+
}
3142+
31153143
if ((trigdesc&&trigdesc->trig_update_after_row)||
31163144
(transition_capture&&
31173145
(transition_capture->tcs_update_old_table||

‎src/backend/optimizer/plan/createplan.c‎

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6988,6 +6988,8 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
69886988
List*mergeActionLists,intepqParam)
69896989
{
69906990
ModifyTable*node=makeNode(ModifyTable);
6991+
booltransition_tables= false;
6992+
booltransition_tables_valid= false;
69916993
List*fdw_private_list;
69926994
Bitmapset*direct_modify_plans;
69936995
ListCell*lc;
@@ -7131,7 +7133,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
71317133
* callback functions needed for that and (2) there are no local
71327134
* structures that need to be run for each modified row: row-level
71337135
* triggers on the foreign table, stored generated columns, WITH CHECK
7134-
* OPTIONs from parent views.
7136+
* OPTIONs from parent views, transition tables on the named relation.
71357137
*/
71367138
direct_modify= false;
71377139
if (fdwroutine!=NULL&&
@@ -7142,7 +7144,19 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
71427144
withCheckOptionLists==NIL&&
71437145
!has_row_triggers(root,rti,operation)&&
71447146
!has_stored_generated_columns(root,rti))
7145-
direct_modify=fdwroutine->PlanDirectModify(root,node,rti,i);
7147+
{
7148+
/* transition_tables is the same for all result relations */
7149+
if (!transition_tables_valid)
7150+
{
7151+
transition_tables=has_transition_tables(root,
7152+
nominalRelation,
7153+
operation);
7154+
transition_tables_valid= true;
7155+
}
7156+
if (!transition_tables)
7157+
direct_modify=fdwroutine->PlanDirectModify(root,node,
7158+
rti,i);
7159+
}
71467160
if (direct_modify)
71477161
direct_modify_plans=bms_add_member(direct_modify_plans,i);
71487162

‎src/backend/optimizer/util/plancat.c‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2252,6 +2252,60 @@ has_row_triggers(PlannerInfo *root, Index rti, CmdType event)
22522252
returnresult;
22532253
}
22542254

2255+
/*
2256+
* has_transition_tables
2257+
*
2258+
* Detect whether the specified relation has any transition tables for event.
2259+
*/
2260+
bool
2261+
has_transition_tables(PlannerInfo*root,Indexrti,CmdTypeevent)
2262+
{
2263+
RangeTblEntry*rte=planner_rt_fetch(rti,root);
2264+
Relationrelation;
2265+
TriggerDesc*trigDesc;
2266+
boolresult= false;
2267+
2268+
Assert(rte->rtekind==RTE_RELATION);
2269+
2270+
/* Currently foreign tables cannot have transition tables */
2271+
if (rte->relkind==RELKIND_FOREIGN_TABLE)
2272+
returnresult;
2273+
2274+
/* Assume we already have adequate lock */
2275+
relation=table_open(rte->relid,NoLock);
2276+
2277+
trigDesc=relation->trigdesc;
2278+
switch (event)
2279+
{
2280+
caseCMD_INSERT:
2281+
if (trigDesc&&
2282+
trigDesc->trig_insert_new_table)
2283+
result= true;
2284+
break;
2285+
caseCMD_UPDATE:
2286+
if (trigDesc&&
2287+
(trigDesc->trig_update_old_table||
2288+
trigDesc->trig_update_new_table))
2289+
result= true;
2290+
break;
2291+
caseCMD_DELETE:
2292+
if (trigDesc&&
2293+
trigDesc->trig_delete_old_table)
2294+
result= true;
2295+
break;
2296+
/* There is no separate event for MERGE, only INSERT/UPDATE/DELETE */
2297+
caseCMD_MERGE:
2298+
result= false;
2299+
break;
2300+
default:
2301+
elog(ERROR,"unrecognized CmdType: %d", (int)event);
2302+
break;
2303+
}
2304+
2305+
table_close(relation,NoLock);
2306+
returnresult;
2307+
}
2308+
22552309
/*
22562310
* has_stored_generated_columns
22572311
*

‎src/include/optimizer/plancat.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,8 @@ extern double get_function_rows(PlannerInfo *root, Oid funcid, Node *node);
7272

7373
externboolhas_row_triggers(PlannerInfo*root,Indexrti,CmdTypeevent);
7474

75+
externboolhas_transition_tables(PlannerInfo*root,Indexrti,CmdTypeevent);
76+
7577
externboolhas_stored_generated_columns(PlannerInfo*root,Indexrti);
7678

7779
externBitmapset*get_dependent_generated_columns(PlannerInfo*root,Indexrti,

0 commit comments

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