8000 Invalidate cached plans on FDW option changes. · hackingwu/postgres@e4380e4 · GitHub
[go: up one dir, main page]
Skip to content

Commit e4380e4

Browse files
tglsfdctglsfdc
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 of forcing a relcache flush on the table. For ALTER FOREIGN DATA WRAPPER and ALTER SERVER, just flush the whole plan cache on any change in pg_foreign_data_wrapper or pg_foreign_server. That matches the way we handle some other low-probability cases such as opclass changes, and it'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 with confidence. Back-patch to 9.3. In principle we could apply the code change to 9.2 as well, but (a) we lack postgres_fdw to test it with, (b) it's doubtful that anyone is doing anything exciting enough with FDWs that far back to need this desperately, and (c) the patch doesn't apply cleanly. Patch originally by Amit Langote, reviewed by Etsuro Fujita and Ashutosh Bapat, who each contributed substantial changes as well. Discussion: https://postgr.es/m/CAKcux6m5cA6rRPTKkqVdJ-R=KKDfe35Q_ZuUqxDSV_4hwga=og@mail.gmail.com
1 parent 4e44656 commit e4380e4

File tree

4 files changed

+80
-0
lines changed

4 files changed

+80
-0
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -853,12 +853,69 @@ EXECUTE st5('foo', 1);
853853
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
854854
(1 row)
855855

856+
-- altering FDW options requires replanning
857+
PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
858+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
859+
QUERY PLAN
860+
----------------------------------------------------------------------------------------------
861+
Foreign Scan on public.ft1 t1
862+
Output: c1, c2, c3, c4, c5, c6, c7, c8
863+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = c2))
864+
(3 rows)
865+
866+
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
867+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
868+
QUERY PLAN
869+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
870+
Insert on public.ft1
871+
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)
872+
-> Result
873+
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
874+
(4 rows)
875+
876+
ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
877+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0');
878+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
879+
QUERY PLAN
880+
----------------------------------------------------------------------------------------------
881+
Foreign Scan on public.ft1 t1
882+
Output: c1, c2, c3, c4, c5, c6, c7, c8
883+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 0" WHERE (("C 1" = c2))
884+
(3 rows)
885+
886+
EXECUTE st6;
887+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
888+
----+----+-------+------------------------------+--------------------------+----+------------+-----
889+
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
890+
2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
891+
3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
892+
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
893+
5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo
894+
6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo
895+
7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
896+
8 | 8 | 00008 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo
897+
9 | 9 | 00009 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9 | foo
898+
(9 rows)
899+
900+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
901+
QUERY PLAN
902+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
903+
Insert on public.ft1
904+
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)
905+
-> Result
906+
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
907+
(4 rows)
908+
909+
ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
910+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1');
856911
-- cleanup
857912
DEALLOCATE st1;
858913
DEALLOCATE st2;
859914
DEALLOCATE st3;
860915
DEALLOCATE st4;
861916
DEALLOCATE st5;
917+
DEALLOCATE st6;
918+
DEALLOCATE st7;
862919
-- System columns, except ctid, should not be sent to remote
863920
EXPLAIN (VERBOSE, COSTS false)
864921
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -246,12 +246,27 @@ EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
246246
EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
247247
EXECUTE st5('foo', 1);
248248

249+
-- altering FDW options requires replanning
250+
PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
251+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
252+
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
253+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
254+
ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
255+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0');
256+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
257+
EXECUTE st6;
258+
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
259+
ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
260+
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1');
261+
249262
-- cleanup
250263
DEALLOCATE st1;
251264
DEALLOCATE st2;
252265
DEALLOCATE st3;
253266
DEALLOCATE st4;
254267
DEALLOCATE st5;
268+
DEALLOCATE st6;
269+
DEALLOCATE st7;
255270

256271
-- System columns, except ctid, should not be sent to remote
257272
EXPLAIN (VERBOSE, COSTS false)

‎src/backend/commands/tablecmds.c

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

10750+
/*
10751+
* Invalidate relcache so that all sessions will refresh any cached plans
10752+
* that might depend on the old options.
10753+
*/
10754+
CacheInvalidateRelcache(rel);
10755+
1075010756
InvokeObjectPostAlterHook(ForeignTableRelationId,
1075110757
RelationGetRelid(rel), 0);
1075210758

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

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

122124
/*

0 commit comments

Comments
 (0)
0