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

On Fri, Apr 22, 2016 at 8:44 AM, Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
> 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

Hmm, interesting. Offhand, I don't really see how to make that case
work: postgres_fdw's UPDATE support supposes that the remote relation
has CTIDs. If it doesn't, we're out of luck. The "direct update"
mode might work if we can get that far, but here we're bombing out
during the planning phase, so we never have a chance to try it.

I wouldn't say this is a bug, exactly; more like an unsupported case.
It would be nice to make it work, though, if someone can figure out
how.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company