From: | Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp |
Cc: | tgl(at)sss(dot)pgh(dot)pa(dot)us, fcs1(at)poczta(dot)onet(dot)pl, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #14738: ALTER SERVER for foregin servers not working |
Date: | 2017-07-11 01:37:31 |
Message-ID: | 20170711.103731.25341581.horiguchi.kyotaro@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
Oops! I lost a race.
At Tue, 11 Jul 2017 10:23:05 +0900, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote in <76f1487a-6b8a-61a9-ebd9-8ff047d0ba94(at)lab(dot)ntt(dot)co(dot)jp>
> On 2017/07/11 1:18, Tom Lane wrote:
> > 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.
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2017-07-11 01:38:00 | Re: BUG #14738: ALTER SERVER for foregin servers not working |
Previous Message | Kyotaro HORIGUCHI | 2017-07-11 01:28:40 | Re: BUG #14738: ALTER SERVER for foregin servers not working |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2017-07-11 01:38:00 | Re: BUG #14738: ALTER SERVER for foregin servers not working |
Previous Message | Paul A Jungwirth | 2017-07-11 01:34:12 | Re: New partitioning - some feedback |