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

Commitc52d37c

Browse files
committed
Invalidate cached plans on FDW option changes.
This fixes problems where a plan must change but fails to do so,as seen in a bug report from Rajkumar Raghuwanshi.For ALTER FOREIGN TABLE OPTIONS, do this through the standard method offorcing a relcache flush on the table. For ALTER FOREIGN DATA WRAPPERand ALTER SERVER, just flush the whole plan cache on any change inpg_foreign_data_wrapper or pg_foreign_server. That matches the waywe handle some other low-probability cases such as opclass changes, andit's unclear that the case arises often enough to be worth working harder.Besides, that gives a patch that is simple enough to back-patch withconfidence.Back-patch to 9.3. In principle we could apply the code change to 9.2 aswell, but (a) we lack postgres_fdw to test it with, (b) it's doubtful thatanyone is doing anything exciting enough with FDWs that far back to needthis desperately, and (c) the patch doesn't apply cleanly.Patch originally by Amit Langote, reviewed by Etsuro Fujita and AshutoshBapat, who each contributed substantial changes as well.Discussion:https://postgr.es/m/CAKcux6m5cA6rRPTKkqVdJ-R=KKDfe35Q_ZuUqxDSV_4hwga=og@mail.gmail.com
1 parent0355e6f commitc52d37c

File tree

4 files changed

+118
-0
lines changed

4 files changed

+118
-0
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3575,12 +3575,99 @@ EXECUTE st5('foo', 1);
35753575
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
35763576
(1 row)
35773577

3578+
-- altering FDW options requires replanning
3579+
PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
3580+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
3581+
QUERY PLAN
3582+
----------------------------------------------------------------------------------------------
3583+
Foreign Scan on public.ft1 t1
3584+
Output: c1, c2, c3, c4, c5, c6, c7, c8
3585+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = c2))
3586+
(3 rows)
3587+
3588+
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
3589+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
3590+
QUERY PLAN
3591+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3592+
Insert on public.ft1
3593+
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
3594+
-> Result
3595+
Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft1 '::character(10), NULL::user_enum
3596+
(4 rows)
3597+
3598+
ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
3599+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0');
3600+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
3601+
QUERY PLAN
3602+
----------------------------------------------------------------------------------------------
3603+
Foreign Scan on public.ft1 t1
3604+
Output: c1, c2, c3, c4, c5, c6, c7, c8
3605+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 0" WHERE (("C 1" = c2))
3606+
(3 rows)
3607+
3608+
EXECUTE st6;
3609+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
3610+
----+----+-------+------------------------------+--------------------------+----+------------+-----
3611+
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
3612+
2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
3613+
3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
3614+
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
3615+
5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo
3616+
6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo
3617+
7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
3618+
8 | 8 | 00008 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo
3619+
9 | 9 | 00009 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9 | foo
3620+
(9 rows)
3621+
3622+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
3623+
QUERY PLAN
3624+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3625+
Insert on public.ft1
3626+
Remote SQL: INSERT INTO "S 1"."T 0"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
3627+
-> Result
3628+
Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft1 '::character(10), NULL::user_enum
3629+
(4 rows)
3630+
3631+
ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
3632+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1');
3633+
PREPARE st8 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
3634+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8;
3635+
QUERY PLAN
3636+
-----------------------------------------------------------------------------------------
3637+
Foreign Scan
3638+
Output: (count(c3))
3639+
Relations: Aggregate on (public.ft1 t1)
3640+
Remote SQL: SELECT count(c3) FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2))
3641+
(4 rows)
3642+
3643+
ALTER SERVER loopback OPTIONS (DROP extensions);
3644+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8;
3645+
QUERY PLAN
3646+
-----------------------------------------------------------
3647+
Aggregate
3648+
Output: count(c3)
3649+
-> Foreign Scan on public.ft1 t1
3650+
Output: c3
3651+
Filter: (t1.c1 === t1.c2)
3652+
Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1"
3653+
(6 rows)
3654+
3655+
EXECUTE st8;
3656+
count
3657+
-------
3658+
9
3659+
(1 row)
3660+
3661+
ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
35783662
-- cleanup
35793663
DEALLOCATE st1;
35803664
DEALLOCATE st2;
35813665
DEALLOCATE st3;
35823666
DEALLOCATE st4;
35833667
DEALLOCATE st5;
3668+
DEALLOCATE st6;
3669+
DEALLOCATE st7;
3670+
DEALLOCATE st8;
35843671
-- System columns, except ctid and oid, should not be sent to remote
35853672
EXPLAIN (VERBOSE, COSTS OFF)
35863673
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -881,12 +881,35 @@ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
881881
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo',1);
882882
EXECUTE st5('foo',1);
883883

884+
-- altering FDW options requires replanning
885+
PREPARE st6ASSELECT*FROM ft1 t1WHEREt1.c1=t1.c2;
886+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
887+
PREPARE st7ASINSERT INTO ft1 (c1,c2,c3)VALUES (1001,101,'foo');
888+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
889+
ALTERTABLE"S 1"."T 1" RENAME TO"T 0";
890+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name'T 0');
891+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
892+
EXECUTE st6;
893+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
894+
ALTERTABLE"S 1"."T 0" RENAME TO"T 1";
895+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name'T 1');
896+
897+
PREPARE st8ASSELECTcount(c3)FROM ft1 t1WHEREt1.c1===t1.c2;
898+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8;
899+
ALTER SERVER loopback OPTIONS (DROP extensions);
900+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8;
901+
EXECUTE st8;
902+
ALTER SERVER loopback OPTIONS (ADD extensions'postgres_fdw');
903+
884904
-- cleanup
885905
DEALLOCATE st1;
886906
DEALLOCATE st2;
887907
DEALLOCATE st3;
888908
DEALLOCATE st4;
889909
DEALLOCATE st5;
910+
DEALLOCATE st6;
911+
DEALLOCATE st7;
912+
DEALLOCATE st8;
890913

891914
-- System columns, except ctid and oid, should not be sent to remote
892915
EXPLAIN (VERBOSE, COSTS OFF)

‎src/backend/commands/tablecmds.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11940,6 +11940,12 @@ ATExecGenericOptions(Relation rel, List *options)
1194011940
simple_heap_update(ftrel,&tuple->t_self,tuple);
1194111941
CatalogUpdateIndexes(ftrel,tuple);
1194211942

11943+
/*
11944+
* Invalidate relcache so that all sessions will refresh any cached plans
11945+
* that might depend on the old options.
11946+
*/
11947+
CacheInvalidateRelcache(rel);
11948+
1194311949
InvokeObjectPostAlterHook(ForeignTableRelationId,
1194411950
RelationGetRelid(rel),0);
1194511951

‎src/backend/utils/cache/plancache.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -118,6 +118,8 @@ InitPlanCache(void)
118118
CacheRegisterSyscacheCallback(NAMESPACEOID,PlanCacheSysCallback, (Datum)0);
119119
CacheRegisterSyscacheCallback(OPEROID,PlanCacheSysCallback, (Datum)0);
120120
CacheRegisterSyscacheCallback(AMOPOPID,PlanCacheSysCallback, (Datum)0);
121+
CacheRegisterSyscacheCallback(FOREIGNSERVEROID,PlanCacheSysCallback, (Datum)0);
122+
CacheRegisterSyscacheCallback(FOREIGNDATAWRAPPEROID,PlanCacheSysCallback, (Datum)0);
121123
}
122124

123125
/*

0 commit comments

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