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 6:22 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

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

Thinking loudly:

This error is hard to interpret for a user who doesn't know about ctid.
Till we find a solution, we can at least fail gracefully with an error
something like "DMLs are not supported on foreign tables referring to
views/non-tables on foreign server" is not supported. While creating the
foreign table a user can specify whether the object being referred is
updatable (writable?) or not, Import foreign schema can set the status by
looking at pg_class type entry. The efforts required may not be worth the
usage given that this case is highly unlikely. May be we should just update
the documents saying that a user may encounter such an error if s/he
attempts to update/delete such a foreign table.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company