From 8bf58c0d9bd336868e2d6489f11dc094cad9ad91 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 21 Jul 2017 12:51:38 -0400 Subject: Re-establish postgres_fdw connections after server or user mapping changes. Previously, postgres_fdw would keep on using an existing connection even if the user did ALTER SERVER or ALTER USER MAPPING commands that should affect connection parameters. Teach it to watch for catcache invals on these catalogs and re-establish connections when the relevant catalog entries change. Per bug #14738 from Michal Lis. In passing, clean up some rather crufty decisions in commit ae9bfc5d6 about where fields of ConnCacheEntry should be reset. We now reset all the fields whenever we open a new connection. Kyotaro Horiguchi, reviewed by Ashutosh Bapat and myself. Back-patch to 9.3 where postgres_fdw appeared. Discussion: https://postgr.es/m/20170710113917.7727.10247@wrigleys.postgresql.org --- contrib/postgres_fdw/sql/postgres_fdw.sql | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 509bb547c69..2eca879da5f 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -195,6 +195,26 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); \det+ +-- Test that alteration of server options causes reconnection +SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work +ALTER SERVER loopback OPTIONS (SET dbname 'no such database'); +SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail +DO $d$ + BEGIN + EXECUTE $$ALTER SERVER loopback + OPTIONS (SET dbname '$$||current_database()||$$')$$; + END; +$d$; +SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again + +-- Test that alteration of user mapping options causes reconnection +ALTER USER MAPPING FOR CURRENT_USER SERVER loopback + OPTIONS (ADD user 'no such user'); +SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail +ALTER USER MAPPING FOR CURRENT_USER SERVER loopback + OPTIONS (DROP user); +SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again + -- Now we should be able to run ANALYZE. -- To exercise multiple code paths, we use local stats on ft1 -- and remote-estimate mode on ft2. -- cgit v1.2.3