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

Commit8b6da83

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 parent7dc6ae3 commit8b6da83

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
@@ -6558,6 +6558,25 @@ SELECT * from loc1;
65586558
2 | skidoo triggered !
65596559
(2 rows)
65606560

6561+
EXPLAIN (verbose, costs off)
6562+
UPDATE rem1 set f1 = 10; -- all columns should be transmitted
6563+
QUERY PLAN
6564+
-----------------------------------------------------------------------
6565+
Update on public.rem1
6566+
Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1
6567+
-> Foreign Scan on public.rem1
6568+
Output: 10, f2, ctid, rem1.*
6569+
Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
6570+
(5 rows)
6571+
6572+
UPDATE rem1 set f1 = 10;
6573+
SELECT * from loc1;
6574+
f1 | f2
6575+
----+--------------------------------
6576+
10 | skidoo triggered ! triggered !
6577+
10 | skidoo triggered ! triggered !
6578+
(2 rows)
6579+
65616580
DELETE FROM rem1;
65626581
-- Add a second trigger, to check that the changes are propagated correctly
65636582
-- from trigger to trigger
@@ -6670,7 +6689,7 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
66706689
NOTICE: NEW: (13,"test triggered !")
66716690
ctid
66726691
--------
6673-
(0,27)
6692+
(0,29)
66746693
(1 row)
66756694

66766695
-- cleanup
@@ -6774,10 +6793,10 @@ BEFORE UPDATE ON rem1
67746793
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
67756794
EXPLAIN (verbose, costs off)
67766795
UPDATE rem1 set f2 = ''; -- can't be pushed down
6777-
QUERY PLAN
6778-
---------------------------------------------------------------------
6796+
QUERY PLAN
6797+
-----------------------------------------------------------------------
67796798
Update on public.rem1
6780-
Remote SQL: UPDATE public.loc1 SETf2 = $2 WHERE ctid = $1
6799+
Remote SQL: UPDATE public.loc1 SETf1 = $2, f2 = $3 WHERE ctid = $1
67816800
-> Foreign Scan on public.rem1
67826801
Output: f1, ''::text, ctid, rem1.*
67836802
Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
@@ -7404,12 +7423,12 @@ AFTER UPDATE OR DELETE ON bar2
74047423
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
74057424
explain (verbose, costs off)
74067425
update bar set f2 = f2 + 100;
7407-
QUERY PLAN
7408-
--------------------------------------------------------------------------------------
7426+
QUERY PLAN
7427+
--------------------------------------------------------------------------------------------------------
74097428
Update on public.bar
74107429
Update on public.bar
74117430
Foreign Update on public.bar2
7412-
Remote SQL: UPDATE public.loct2 SETf2 = $2 WHERE ctid = $1 RETURNING f1, f2, f3
7431+
Remote SQL: UPDATE public.loct2 SETf1 = $2, f2 = $3, f3 = $4 WHERE ctid = $1 RETURNING f1, f2, f3
74137432
-> Seq Scan on public.bar
74147433
Output: bar.f1, (bar.f2 + 100), bar.ctid
74157434
-> 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
@@ -1686,12 +1686,19 @@ postgresPlanForeignModify(PlannerInfo *root,
16861686

16871687
/*
16881688
* In an INSERT, we transmit all columns that are defined in the foreign
1689-
* table. In an UPDATE, we transmit only columns that were explicitly
1690-
* targets of the UPDATE, so as to avoid unnecessary data transmission.
1691-
* (We can't do that for INSERT since we would miss sending default values
1692-
* for columns not listed in the source statement.)
1693-
*/
1694-
if (operation==CMD_INSERT)
1689+
* table. In an UPDATE, if there are BEFORE ROW UPDATE triggers on the
1690+
* foreign table, we transmit all columns like INSERT; else we transmit
1691+
* only columns that were explicitly targets of the UPDATE, so as to avoid
1692+
* unnecessary data transmission. (We can't do that for INSERT since we
1693+
* would miss sending default values for columns not listed in the source
1694+
* statement, and for UPDATE if there are BEFORE ROW UPDATE triggers since
1695+
* those triggers might change values for non-target columns, in which
1696+
* case we would miss sending changed values for those columns.)
1697+
*/
1698+
if (operation==CMD_INSERT||
1699+
(operation==CMD_UPDATE&&
1700+
rel->trigdesc&&
1701+
rel->trigdesc->trig_update_before_row))
16951702
{
16961703
TupleDesctupdesc=RelationGetDescr(rel);
16971704
intattnum;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1553,6 +1553,11 @@ SELECT * from loc1;
15531553
UPDATE rem1set f2='skidoo' RETURNING f2;
15541554
SELECT*from loc1;
15551555

1556+
EXPLAIN (verbose, costs off)
1557+
UPDATE rem1set f1=10;-- all columns should be transmitted
1558+
UPDATE rem1set f1=10;
1559+
SELECT*from loc1;
1560+
15561561
DELETEFROM rem1;
15571562

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

0 commit comments

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