On Fri, Apr 22, 2016 at 8:44 AM, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@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
