From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: fcs1@poczta.onet.pl
Cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14738: ALTER SERVER for foregin servers not working
Date: Tue, 11 Jul 2017 10:23:05 +0900
Message-ID: <76f1487a-6b8a-61a9-ebd9-8ff047d0ba94@lab.ntt.co.jp> (raw)
In-Reply-To: <1838.1499703533@sss.pgh.pa.us>
References: <20170710113917.7727.10247@wrigleys.postgresql.org>
<1838.1499703533@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
On 2017/07/11 1:18, Tom Lane wrote:
> fcs1@poczta.onet.pl writes:
>> PostgreSQL version: 9.4.1
>
>> Command like this doesn't take effect:
>> ALTER SERVER srw_egib_1
>> OPTIONS (
>> SET host 'localhost',
>> SET port '5432',
>> SET dbname 'gml2m1');
>> It changes definition of this server but tables connected to it are still
>> connected to previous definition of this server, for example:
>
> It would help if you provided a concrete example of misbehavior rather
> than abstract claims. However, I *think* this is something we fixed in
> 9.4.11.
Perhaps you are referring to the following item fixed in 9.4.11 [1]:
"Ensure that cached plans are invalidated by changes in foreign-table options"
ISTM, OP's problem is unrelated. Steps to reproduce:
create extension postgres_fdw ;
create server s1 foreign data wrapper postgres_fdw options (dbname 'db1');
create server s2 foreign data wrapper postgres_fdw options (dbname 'db2');
create user mapping for current_user server s1;
create user mapping for current_user server s2;
create foreign table t1 (a int) server s1 options (table_name 't1');
-- in db1
create table t1 (a) as select 1;
-- in db2
create table t1 (a) as select 2;
-- back in the original database; t1's server s1 connected to db1
select * from t1;
a
---
1
(1 row)
-- make s1 point to db2
alter server s1 options (set dbname 'db2');
-- postgres_fdw will still connect to db1
select * from t1;
a
---
1
(1 row)
I think that's because postgres_fdw/connection.c keeps a cache of
connections and does not invalidate it upon pg_foreign_server and/or
pg_user_mapping changes. I think we discussed the possibility of fixing
this back when the above-mentioned fix was being worked on [2], but it
went nowhere.
Thanks,
Amit
[1] https://www.postgresql.org/docs/devel/static/release-9-4-11.html
[2]
https://www.postgresql.org/message-id/20160405.184408.166437663.horiguchi.kyotaro%40lab.ntt.co.jp
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, tgl@sss.pgh.pa.us, fcs1@poczta.onet.pl
Subject: Re: BUG #14738: ALTER SERVER for foregin servers not working
In-Reply-To: <76f1487a-6b8a-61a9-ebd9-8ff047d0ba94@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox