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

Commit2144601

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 parentafaa32d commit2144601

File tree

3 files changed

+44
-13
lines changed

3 files changed

+44
-13
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 26 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -6577,6 +6577,25 @@ SELECT * from loc1;
65776577
2 | skidoo triggered !
65786578
(2 rows)
65796579

6580+
EXPLAIN (verbose, costs off)
6581+
UPDATE rem1 set f1 = 10; -- all columns should be transmitted
6582+
QUERY PLAN
6583+
-----------------------------------------------------------------------
6584+
Update on public.rem1
6585+
Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1
6586+
-> Foreign Scan on public.rem1
6587+
Output: 10, f2, ctid, rem1.*
6588+
Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
6589+
(5 rows)
6590+
6591+
UPDATE rem1 set f1 = 10;
6592+
SELECT * from loc1;
6593+
f1 | f2
6594+
----+--------------------------------
6595+
10 | skidoo triggered ! triggered !
6596+
10 | skidoo triggered ! triggered !
6597+
(2 rows)
6598+
65806599
DELETE FROM rem1;
65816600
-- Add a second trigger, to check that the changes are propagated correctly
65826601
-- from trigger to trigger
@@ -6689,7 +6708,7 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
66896708
NOTICE: NEW: (13,"test triggered !")
66906709
ctid
66916710
--------
6692-
(0,27)
6711+
(0,29)
66936712
(1 row)
66946713

66956714
-- cleanup
@@ -6793,10 +6812,10 @@ BEFORE UPDATE ON rem1
67936812
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
67946813
EXPLAIN (verbose, costs off)
67956814
UPDATE rem1 set f2 = ''; -- can't be pushed down
6796-
QUERY PLAN
6797-
---------------------------------------------------------------------
6815+
QUERY PLAN
6816+
-----------------------------------------------------------------------
67986817
Update on public.rem1
6799-
Remote SQL: UPDATE public.loc1 SETf2 = $2 WHERE ctid = $1
6818+
Remote SQL: UPDATE public.loc1 SETf1 = $2, f2 = $3 WHERE ctid = $1
68006819
-> Foreign Scan on public.rem1
68016820
Output: f1, ''::text, ctid, rem1.*
68026821
Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
@@ -7423,12 +7442,12 @@ AFTER UPDATE OR DELETE ON bar2
74237442
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
74247443
explain (verbose, costs off)
74257444
update bar set f2 = f2 + 100;
7426-
QUERY PLAN
7427-
--------------------------------------------------------------------------------------
7445+
QUERY PLAN
7446+
--------------------------------------------------------------------------------------------------------
74287447
Update on public.bar
74297448
Update on public.bar
74307449
Foreign Update on public.bar2
7431-
Remote SQL: UPDATE public.loct2 SETf2 = $2 WHERE ctid = $1 RETURNING f1, f2, f3
7450+
Remote SQL: UPDATE public.loct2 SETf1 = $2, f2 = $3, f3 = $4 WHERE ctid = $1 RETURNING f1, f2, f3
74327451
-> Seq Scan on public.bar
74337452
Output: bar.f1, (bar.f2 + 100), bar.ctid
74347453
-> Foreign Scan on public.bar2

‎contrib/postgres_fdw/postgres_fdw.c

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

16211621
/*
16221622
* In an INSERT, we transmit all columns that are defined in the foreign
1623-
* table. In an UPDATE, we transmit only columns that were explicitly
1624-
* targets of the UPDATE, so as to avoid unnecessary data transmission.
1625-
* (We can't do that for INSERT since we would miss sending default values
1626-
* for columns not listed in the source statement.)
1627-
*/
1628-
if (operation==CMD_INSERT)
1623+
* table. In an UPDATE, if there are BEFORE ROW UPDATE triggers on the
1624+
* foreign table, we transmit all columns like INSERT; else we transmit
1625+
* only columns that were explicitly targets of the UPDATE, so as to avoid
1626+
* unnecessary data transmission. (We can't do that for INSERT since we
1627+
* would miss sending default values for columns not listed in the source
1628+
* statement, and for UPDATE if there are BEFORE ROW UPDATE triggers since
1629+
* those triggers might change values for non-target columns, in which
1630+
* case we would miss sending changed values for those columns.)
1631+
*/
1632+
if (operation==CMD_INSERT||
1633+
(operation==CMD_UPDATE&&
1634+
rel->trigdesc&&
1635+
rel->trigdesc->trig_update_before_row))
16291636
{
16301637
TupleDesctupdesc=RelationGetDescr(rel);
16311638
intattnum;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1481,6 +1481,11 @@ SELECT * from loc1;
14811481
UPDATE rem1set f2='skidoo' RETURNING f2;
14821482
SELECT*from loc1;
14831483

1484+
EXPLAIN (verbose, costs off)
1485+
UPDATE rem1set f1=10;-- all columns should be transmitted
1486+
UPDATE rem1set f1=10;
1487+
SELECT*from loc1;
1488+
14841489
DELETEFROM rem1;
14851490

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

0 commit comments

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