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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers