foreign-plan-inval.patch
text/x-patch
Filename: foreign-plan-inval.patch
Type: text/x-patch
Part: 0
Patch
Format: context
| File | + | − |
|---|---|---|
| contrib/postgres_fdw/expected/postgres_fdw.out | 168 | 0 |
| contrib/postgres_fdw/sql/postgres_fdw.sql | 38 | 0 |
| src/backend/commands/tablecmds.c | 6 | 0 |
| src/backend/utils/cache/plancache.c | 2 | 0 |
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 3575,3586 **** EXECUTE st5('foo', 1);
--- 3575,3754 ----
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
(1 row)
+ -- altering FDW options requires replanning
+ PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = c2))
+ (3 rows)
+
+ PREPARE st7(int,int,text) AS INSERT INTO ft1 (c1,c2,c3) VALUES ($1,$2,$3);
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7(1001,101,'foo');
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Insert on public.ft1
+ 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)
+ -> Result
+ 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
+ (4 rows)
+
+ PREPARE st8(int) AS UPDATE ft1 SET c3 = 'bar' WHERE c1 = $1 RETURNING *;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8(1001);
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Update on public.ft1
+ Remote SQL: UPDATE "S 1"."T 1" SET c3 = 'bar'::text WHERE (("C 1" = 1001)) RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ (4 rows)
+
+ PREPARE st9(int) AS DELETE FROM ft1 WHERE c1 = $1 RETURNING *;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st9(1001);
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------------------------
+ Delete on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Delete on public.ft1
+ Remote SQL: DELETE FROM "S 1"."T 1" WHERE (("C 1" = 1001)) RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ (4 rows)
+
+ ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
+ ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0');
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 0" WHERE (("C 1" = c2))
+ (3 rows)
+
+ EXECUTE st6;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+ ----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
+ 2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
+ 3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
+ 4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
+ 5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo
+ 6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo
+ 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
+ 8 | 8 | 00008 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo
+ 9 | 9 | 00009 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9 | foo
+ (9 rows)
+
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7(1001,101,'foo');
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Insert on public.ft1
+ 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)
+ -> Result
+ 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
+ (4 rows)
+
+ EXECUTE st7(1001,101,'foo');
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8(1001);
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Update on public.ft1
+ Remote SQL: UPDATE "S 1"."T 0" SET c3 = 'bar'::text WHERE (("C 1" = 1001)) RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ (4 rows)
+
+ EXECUTE st8(1001);
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+ ------+-----+-----+----+----+----+------------+----
+ 1001 | 101 | bar | | | | ft1 |
+ (1 row)
+
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st9(1001);
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------------------------
+ Delete on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Delete on public.ft1
+ Remote SQL: DELETE FROM "S 1"."T 0" WHERE (("C 1" = 1001)) RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ (4 rows)
+
+ EXECUTE st9(1001);
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+ ------+-----+-----+----+----+----+------------+----
+ 1001 | 101 | bar | | | | ft1 |
+ (1 row)
+
+ ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
+ ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1');
+ PREPARE st10 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st10;
+ QUERY PLAN
+ -----------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (count(c3))
+ Relations: Aggregate on (public.ft1 t1)
+ Remote SQL: SELECT count(c3) FROM "S 1"."T 1" WHERE (("C 1" = public.postgres_fdw_abs(c2)))
+ (4 rows)
+
+ PREPARE st11 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st11;
+ QUERY PLAN
+ -----------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (count(c3))
+ Relations: Aggregate on (public.ft1 t1)
+ Remote SQL: SELECT count(c3) FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2))
+ (4 rows)
+
+ ALTER SERVER loopback OPTIONS (DROP extensions);
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st10;
+ QUERY PLAN
+ -----------------------------------------------------------
+ Aggregate
+ Output: count(c3)
+ -> Foreign Scan on public.ft1 t1
+ Output: c3
+ Filter: (t1.c1 = postgres_fdw_abs(t1.c2))
+ Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1"
+ (6 rows)
+
+ EXECUTE st10;
+ count
+ -------
+ 9
+ (1 row)
+
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st11;
+ QUERY PLAN
+ -----------------------------------------------------------
+ Aggregate
+ Output: count(c3)
+ -> Foreign Scan on public.ft1 t1
+ Output: c3
+ Filter: (t1.c1 === t1.c2)
+ Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1"
+ (6 rows)
+
+ EXECUTE st11;
+ count
+ -------
+ 9
+ (1 row)
+
+ ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
-- cleanup
DEALLOCATE st1;
DEALLOCATE st2;
DEALLOCATE st3;
DEALLOCATE st4;
DEALLOCATE st5;
+ DEALLOCATE st6;
+ DEALLOCATE st7;
+ DEALLOCATE st8;
+ DEALLOCATE st9;
+ DEALLOCATE st10;
+ DEALLOCATE st11;
-- System columns, except ctid and oid, should not be sent to remote
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 881,892 **** EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
--- 881,930 ----
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
EXECUTE st5('foo', 1);
+ -- altering FDW options requires replanning
+ PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
+ PREPARE st7(int,int,text) AS INSERT INTO ft1 (c1,c2,c3) VALUES ($1,$2,$3);
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7(1001,101,'foo');
+ PREPARE st8(int) AS UPDATE ft1 SET c3 = 'bar' WHERE c1 = $1 RETURNING *;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8(1001);
+ PREPARE st9(int) AS DELETE FROM ft1 WHERE c1 = $1 RETURNING *;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st9(1001);
+ ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
+ ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0');
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
+ EXECUTE st6;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7(1001,101,'foo');
+ EXECUTE st7(1001,101,'foo');
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8(1001);
+ EXECUTE st8(1001);
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st9(1001);
+ EXECUTE st9(1001);
+ ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
+ ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1');
+ PREPARE st10 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st10;
+ PREPARE st11 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st11;
+ ALTER SERVER loopback OPTIONS (DROP extensions);
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st10;
+ EXECUTE st10;
+ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st11;
+ EXECUTE st11;
+ ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
+
-- cleanup
DEALLOCATE st1;
DEALLOCATE st2;
DEALLOCATE st3;
DEALLOCATE st4;
DEALLOCATE st5;
+ DEALLOCATE st6;
+ DEALLOCATE st7;
+ DEALLOCATE st8;
+ DEALLOCATE st9;
+ DEALLOCATE st10;
+ DEALLOCATE st11;
-- System columns, except ctid and oid, should not be sent to remote
EXPLAIN (VERBOSE, COSTS OFF)
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 11266,11271 **** ATExecGenericOptions(Relation rel, List *options)
--- 11266,11277 ----
simple_heap_update(ftrel, &tuple->t_self, tuple);
CatalogUpdateIndexes(ftrel, tuple);
+ /*
+ * Invalidate relcache so that after this commit all sessions will
+ * refresh any cached plans that might reference the old options
+ */
+ CacheInvalidateRelcache(rel);
+
InvokeObjectPostAlterHook(ForeignTableRelationId,
RelationGetRelid(rel), 0);
*** a/src/backend/utils/cache/plancache.c
--- b/src/backend/utils/cache/plancache.c
***************
*** 118,123 **** InitPlanCache(void)
--- 118,125 ----
CacheRegisterSyscacheCallback(NAMESPACEOID, PlanCacheSysCallback, (Datum) 0);
CacheRegisterSyscacheCallback(OPEROID, PlanCacheSysCallback, (Datum) 0);
CacheRegisterSyscacheCallback(AMOPOPID, PlanCacheSysCallback, (Datum) 0);
+ CacheRegisterSyscacheCallback(FOREIGNSERVEROID, PlanCacheSysCallback, (Datum) 0);
+ CacheRegisterSyscacheCallback(FOREIGNDATAWRAPPEROID, PlanCacheSysCallback, (Datum) 0);
}
/*