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

Commit1a3d9f6

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Account for triggers in non-direct remote UPDATE planning.
Previously, in postgresPlanForeignModify, we planned an UPDATE operationon a foreign table so that we transmit only columns that were explicitlytargets of the UPDATE, so as to avoid unnecessary data transmission, butif there were BEFORE ROW UPDATE triggers on the foreign table, thosetriggers might change values for non-target columns, in which case wewould miss sending changed values for those columns. Prevent optimizingaway transmitting all columns if there are BEFORE ROW UPDATE triggers onthe foreign table.This is an oversight in commit7cbe57c which added triggers on foreigntables, so apply the patch all the way back to 9.4 where that came in.Author: Shohei MochizukiReviewed-by: Amit LangoteDiscussion:https://postgr.es/m/201905270152.x4R1q3qi014550@toshiba.co.jp
1 parentdf88137 commit1a3d9f6

File tree

3 files changed

+38
-7
lines changed

3 files changed

+38
-7
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3063,6 +3063,25 @@ SELECT * from loc1;
30633063
2 | skidoo triggered !
30643064
(2 rows)
30653065

3066+
EXPLAIN (verbose, costs off)
3067+
UPDATE rem1 set f1 = 10; -- all columns should be transmitted
3068+
QUERY PLAN
3069+
-----------------------------------------------------------------------
3070+
Update on public.rem1
3071+
Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1
3072+
-> Foreign Scan on public.rem1
3073+
Output: 10, f2, ctid, rem1.*
3074+
Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
3075+
(5 rows)
3076+
3077+
UPDATE rem1 set f1 = 10;
3078+
SELECT * from loc1;
3079+
f1 | f2
3080+
----+--------------------------------
3081+
10 | skidoo triggered ! triggered !
3082+
10 | skidoo triggered ! triggered !
3083+
(2 rows)
3084+
30663085
DELETE FROM rem1;
30673086
-- Add a second trigger, to check that the changes are propagated correctly
30683087
-- from trigger to trigger
@@ -3175,6 +3194,6 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
31753194
NOTICE: NEW: (13,"test triggered !")
31763195
ctid
31773196
--------
3178-
(0,27)
3197+
(0,29)
31793198
(1 row)
31803199

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1176,12 +1176,19 @@ postgresPlanForeignModify(PlannerInfo *root,
11761176

11771177
/*
11781178
* In an INSERT, we transmit all columns that are defined in the foreign
1179-
* table. In an UPDATE, we transmit only columns that were explicitly
1180-
* targets of the UPDATE, so as to avoid unnecessary data transmission.
1181-
* (We can't do that for INSERT since we would miss sending default values
1182-
* for columns not listed in the source statement.)
1183-
*/
1184-
if (operation==CMD_INSERT)
1179+
* table. In an UPDATE, if there are BEFORE ROW UPDATE triggers on the
1180+
* foreign table, we transmit all columns like INSERT; else we transmit
1181+
* only columns that were explicitly targets of the UPDATE, so as to avoid
1182+
* unnecessary data transmission. (We can't do that for INSERT since we
1183+
* would miss sending default values for columns not listed in the source
1184+
* statement, and for UPDATE if there are BEFORE ROW UPDATE triggers since
1185+
* those triggers might change values for non-target columns, in which
1186+
* case we would miss sending changed values for those columns.)
1187+
*/
1188+
if (operation==CMD_INSERT||
1189+
(operation==CMD_UPDATE&&
1190+
rel->trigdesc&&
1191+
rel->trigdesc->trig_update_before_row))
11851192
{
11861193
TupleDesctupdesc=RelationGetDescr(rel);
11871194
intattnum;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -638,6 +638,11 @@ SELECT * from loc1;
638638
UPDATE rem1set f2='skidoo' RETURNING f2;
639639
SELECT*from loc1;
640640

641+
EXPLAIN (verbose, costs off)
642+
UPDATE rem1set f1=10;-- all columns should be transmitted
643+
UPDATE rem1set f1=10;
644+
SELECT*from loc1;
645+
641646
DELETEFROM rem1;
642647

643648
-- Add a second trigger, to check that the changes are propagated correctly

0 commit comments

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