On Fri, Apr 22, 2016 at 6:22 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Fri, Apr 22, 2016 at 8:44 AM, Rajkumar Raghuwanshi > <rajkumar.raghuwan...@enterprisedb.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
