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

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

On Mon, Apr 25, 2016 at 2:54 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> On Fri, Apr 22, 2016 at 6:22 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> 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.

I would be disinclined to add code specifically to catch this, since
the only report we have so far is from someone whose job is to find
corner cases that break.  Which he did, and good for him, but like you
say, that doesn't mean it's a big real-world problem.  Adding a
sentence to the documentation stating that pointing a foreign table at
a view is fine for SELECT, but that UPDATE and DELETE may not work,
seems like enough.

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

On 2016/04/26 12:52, Robert Haas wrote:
> On Mon, Apr 25, 2016 at 2:54 AM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
>> 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.

> I would be disinclined to add code specifically to catch this, since
> the only report we have so far is from someone whose job is to find
> corner cases that break.  Which he did, and good for him, but like you
> say, that doesn't mean it's a big real-world problem.  Adding a
> sentence to the documentation stating that pointing a foreign table at
> a view is fine for SELECT, but that UPDATE and DELETE may not work,
> seems like enough.

+1 for just updating the documentation.

Best regards,
Etsuro Fujita