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

Commit0f2b234

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 parent909a92e commit0f2b234

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
@@ -6398,6 +6398,25 @@ SELECT * from loc1;
63986398
2 | skidoo triggered !
63996399
(2 rows)
64006400

6401+
EXPLAIN (verbose, costs off)
6402+
UPDATE rem1 set f1 = 10; -- all columns should be transmitted
6403+
QUERY PLAN
6404+
-----------------------------------------------------------------------
6405+
Update on public.rem1
6406+
Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1
6407+
-> Foreign Scan on public.rem1
6408+
Output: 10, f2, ctid, rem1.*
6409+
Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
6410+
(5 rows)
6411+
6412+
UPDATE rem1 set f1 = 10;
6413+
SELECT * from loc1;
6414+
f1 | f2
6415+
----+--------------------------------
6416+
10 | skidoo triggered ! triggered !
6417+
10 | skidoo triggered ! triggered !
6418+
(2 rows)
6419+
64016420
DELETE FROM rem1;
64026421
-- Add a second trigger, to check that the changes are propagated correctly
64036422
-- from trigger to trigger
@@ -6510,7 +6529,7 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1
65106529
NOTICE: NEW: (13,"test triggered !")
65116530
ctid
65126531
--------
6513-
(0,27)
6532+
(0,29)
65146533
(1 row)
65156534

65166535
-- cleanup
@@ -6614,10 +6633,10 @@ BEFORE UPDATE ON rem1
66146633
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
66156634
EXPLAIN (verbose, costs off)
66166635
UPDATE rem1 set f2 = ''; -- can't be pushed down
6617-
QUERY PLAN
6618-
---------------------------------------------------------------------
6636+
QUERY PLAN
6637+
-----------------------------------------------------------------------
66196638
Update on public.rem1
6620-
Remote SQL: UPDATE public.loc1 SETf2 = $2 WHERE ctid = $1
6639+
Remote SQL: UPDATE public.loc1 SETf1 = $2, f2 = $3 WHERE ctid = $1
66216640
-> Foreign Scan on public.rem1
66226641
Output: f1, ''::text, ctid, rem1.*
66236642
Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
@@ -7238,12 +7257,12 @@ AFTER UPDATE OR DELETE ON bar2
72387257
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
72397258
explain (verbose, costs off)
72407259
update bar set f2 = f2 + 100;
7241-
QUERY PLAN
7242-
--------------------------------------------------------------------------------------
7260+
QUERY PLAN
7261+
--------------------------------------------------------------------------------------------------------
72437262
Update on public.bar
72447263
Update on public.bar
72457264
Foreign Update on public.bar2
7246-
Remote SQL: UPDATE public.loct2 SETf2 = $2 WHERE ctid = $1 RETURNING f1, f2, f3
7265+
Remote SQL: UPDATE public.loct2 SETf1 = $2, f2 = $3, f3 = $4 WHERE ctid = $1 RETURNING f1, f2, f3
72477266
-> Seq Scan on public.bar
72487267
Output: bar.f1, (bar.f2 + 100), bar.ctid
72497268
-> 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
@@ -1583,12 +1583,19 @@ postgresPlanForeignModify(PlannerInfo *root,
15831583

15841584
/*
15851585
* In an INSERT, we transmit all columns that are defined in the foreign
1586-
* table. In an UPDATE, we transmit only columns that were explicitly
1587-
* targets of the UPDATE, so as to avoid unnecessary data transmission.
1588-
* (We can't do that for INSERT since we would miss sending default values
1589-
* for columns not listed in the source statement.)
1590-
*/
1591-
if (operation==CMD_INSERT)
1586+
* table. In an UPDATE, if there are BEFORE ROW UPDATE triggers on the
1587+
* foreign table, we transmit all columns like INSERT; else we transmit
1588+
* only columns that were explicitly targets of the UPDATE, so as to avoid
1589+
* unnecessary data transmission. (We can't do that for INSERT since we
1590+
* would miss sending default values for columns not listed in the source
1591+
* statement, and for UPDATE if there are BEFORE ROW UPDATE triggers since
1592+
* those triggers might change values for non-target columns, in which
1593+
* case we would miss sending changed values for those columns.)
1594+
*/
1595+
if (operation==CMD_INSERT||
1596+
(operation==CMD_UPDATE&&
1597+
rel->trigdesc&&
1598+
rel->trigdesc->trig_update_before_row))
15921599
{
15931600
TupleDesctupdesc=RelationGetDescr(rel);
15941601
intattnum;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1419,6 +1419,11 @@ SELECT * from loc1;
14191419
UPDATE rem1set f2='skidoo' RETURNING f2;
14201420
SELECT*from loc1;
14211421

1422+
EXPLAIN (verbose, costs off)
1423+
UPDATE rem1set f1=10;-- all columns should be transmitted
1424+
UPDATE rem1set f1=10;
1425+
SELECT*from loc1;
1426+
14221427
DELETEFROM rem1;
14231428

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

0 commit comments

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