Re: BUG #14738: ALTER SERVER for foregin servers not working

At Mon, 10 Jul 2017 12:18:53 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in <1838(dot)1499703533(at)sss(dot)pgh(dot)pa(dot)us>
> fcs1(at)poczta(dot)onet(dot)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. Please update and try again.

Since 9.4.11 plancache is invalidated by ALTER SERVER but the
master still doesn't disconnect existing connection by ALTER
SERVER like that.

create server sv1 foreign data wrapper postgres_fdw options (host '/tmp', port '5432', dbname 'postgres');
create user mapping for public server sv1;
create table t1 (a int);
create foreign table ft1 (a int) server sv1 options (table_name 't1');
insert into t1 values (1), (2), (3);
select * from ft1;
<returns 1, 2, 3>
alter server sv1 options (set host 'hoge');
insert into t1 values (4);
select * from ft1;
<returns 1, 2, 3, 4, still on the old connection>

\c -- reconnect backend
select * from ft1;
ERROR: could not connect to server "sv1"
DETAIL: could not translate host name "hoge" to address: Name or service not known

I faintly recall such discussion was held aroud that time and
maybe we concluded that we don't do that but I haven't find such
a thread in pgsql-hackers..

If we are to "fix" this, GetConnection() of postgres_fdw needs to
recheck foreign server options, or add an FDW interface to notify
such option changes.

regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center