[HACKERS] postgres_fdw : altering foreign table not invalidating prepare statement execution plan.

[Rajkumar Raghuwanshi]

Hi,

I observed below in postgres_fdw

* .Observation: *Prepare statement execution plan is not getting changed
even after altering foreign table to point to new schema.

CREATE EXTENSION postgres_fdw;
CREATE SCHEMA s1;
create table s1.lt (c1 integer, c2 varchar);
insert into s1.lt values (1, 's1.lt');
CREATE SCHEMA s2;
create table s2.lt (c1 integer, c2 varchar);
insert into s2.lt values (1, 's2.lt');
CREATE SERVER link_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname
'postgres', port '5447', use_remote_estimate 'true');
CREATE USER MAPPING FOR public SERVER link_server;

create foreign table ft (c1 integer, c2 varchar) server link_server options
(schema_name 's1',table_name 'lt');

ANALYZE ft;
PREPARE stmt_ft AS select c1,c2 from ft;

EXECUTE stmt_ft;
 c1 |  c2
----+-------
  1 | s1.lt
(1 row)

--changed foreign table ft pointing schema from s1 to s2
ALTER foreign table ft options (SET schema_name 's2', SET table_name 'lt');
ANALYZE ft;

EXPLAIN (COSTS OFF, VERBOSE) EXECUTE stmt_ft;
               QUERY PLAN
----------------------------------------
 Foreign Scan on public.ft
   Output: c1, c2
   Remote SQL: SELECT c1, c2 FROM s1.lt
(3 rows)

EXECUTE stmt_ft;
 c1 |  c2
----+-------
  1 | s1.lt
(1 row)

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

>