postgres_fdw : Not able to update foreign table referring to a local table's view when use_remote_estimate = true

From: Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: postgres_fdw : Not able to update foreign table referring to a local table's view when use_remote_estimate = true
Date: 2016-04-22 12:44:59
Message-ID: CAKcux6n-mpfO+wLXM1vn6-x4E2TkMepSNjLaT=aUNJOR=SNLAw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I observed below in postgres_fdw.

*Observation:* Update a foreign table which is referring to a local table's
view (with use_remote_estimate = true) getting failed with below error.
ERROR: column "ctid" does not exist
CONTEXT: Remote SQL command: EXPLAIN SELECT c1, ctid
FROM public.lt_view FOR UPDATE

create extension postgres_fdw;
create server link_server foreign data wrapper postgres_fdw options (host
'localhost',dbname 'postgres', port '5447');
create user mapping for public server link_server;

create table lt (c1 integer, c2 integer);
insert into lt values (1,null);
create view lt_view as select * from lt;
create foreign table ft (c1 integer,c2 integer) server link_server options
(table_name 'lt_view');

--alter server with use_remote_estimate 'false'
alter server link_server options (add use_remote_estimate 'false');
--update foreign table refering to local view -- able to update
update ft set c2 = c1;
UPDATE 1

--alter server with use_remote_estimate 'true'
alter server link_server options (SET use_remote_estimate 'true');
--update foreign table refering to local view -- fail, throwing error
update ft set c2 = c1;
psql:/home/edb/Desktop/edb_work/Postgres_Fdw/dml_pushdown_35882/observation_view.sql:24:
ERROR: column "ctid" does not exist
CONTEXT: Remote SQL command: EXPLAIN SELECT c1, ctid FROM public.lt_view
FOR UPDATE

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

>

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-04-22 12:52:30 Re: postgres_fdw : Not able to update foreign table referring to a local table's view when use_remote_estimate = true
Previous Message Juergen Hannappel 2016-04-22 10:42:59 [PATCH] add option to pg_dumpall to exclude tables from the dump