Обсуждение: [BUGS] BUG #14738: ALTER SERVER for foregin servers not working

The following bug has been logged on the website:

Bug reference:      14738
Logged by:          Michal L
Email address:      fcs1@poczta.onet.pl
PostgreSQL version: 9.4.1
Operating system:   Windows 7 x64 Pro
Description:

Hello,

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:

CREATE SERVER srw_egib_1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (   host 'localhost',   port '5432',   dbname
'gml2');

It seams to take effect when I disconnect to the database and connect again.
I tested it on PgAdmin.

Regards
Michal


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.  Please update and try again.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-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

At Mon, 10 Jul 2017 12:18:53 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote in <1838.1499703533@sss.pgh.pa.us>
> 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.  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



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Oops! I lost a race.

At Tue, 11 Jul 2017 10:23:05 +0900, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote in
<76f1487a-6b8a-61a9-ebd9-8ff047d0ba94@lab.ntt.co.jp>
> 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:

Agreed.

> 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

Many thanks for digging out it, that have almost faded out of my
memory..

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Horiguchi-san,

On 2017/07/11 10:28, Kyotaro HORIGUCHI wrote:
> 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..

I mentioned it in my reply.  Here again:

https://www.postgresql.org/message-id/20160405.184408.166437663.horiguchi.kyotaro%40lab.ntt.co.jp

Thanks,
Amit



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Horiguchi-san,

On 2017/07/11 10:28, Kyotaro HORIGUCHI wrote:

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..

I mentioned it in my reply.  Here again:

https://www.postgresql.org/message-id/20160405.184408.166437663.horiguchi.kyotaro%40lab.ntt.co.jp

Thanks,
Amit

Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> Horiguchi-san,
> On 2017/07/11 10:28, Kyotaro HORIGUCHI wrote:
>> 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..

> I mentioned it in my reply.  Here again:
> https://www.postgresql.org/message-id/20160405.184408.166437663.horiguchi.kyotaro%40lab.ntt.co.jp

The followup discussion noted that that approach was no good because it
would only close connections in the same session that had done the ALTER
SERVER.  I think the basic idea of marking postgres_fdw connections as
needing to be remade when next possible is OK, but we have to drive it
off catcache invalidation events, the same as we did in c52d37c8b.  An
advantage of that way is we don't need any new hooks in the core code.

Kyotaro-san, are you planning to update your old patch?
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

At Tue, 11 Jul 2017 15:39:14 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote in <6234.1499801954@sss.pgh.pa.us>
> Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> > Horiguchi-san,
> > On 2017/07/11 10:28, Kyotaro HORIGUCHI wrote:
> >> 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..
> 
> > I mentioned it in my reply.  Here again:
> > https://www.postgresql.org/message-id/20160405.184408.166437663.horiguchi.kyotaro%40lab.ntt.co.jp
> 
> The followup discussion noted that that approach was no good because it
> would only close connections in the same session that had done the ALTER
> SERVER.  I think the basic idea of marking postgres_fdw connections as
> needing to be remade when next possible is OK, but we have to drive it
> off catcache invalidation events, the same as we did in c52d37c8b.  An
> advantage of that way is we don't need any new hooks in the core code.
> 
> Kyotaro-san, are you planning to update your old patch?

I'm pleased to do that. I will reconsider the way shown in a mail
in the thread soon.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs