Обсуждение: [BUGS] BUG #14770: postgres_fdw assumes foreign table is in postgres

The following bug has been logged on the website:

Bug reference:      14770
Logged by:          Ross Biro
Email address:      rbiro@interfacefinancial.com
PostgreSQL version: 9.6.3
Operating system:   Linux
Description:

Using postgres_fdw seems to assume that the foreign table is not also a
foreign table in the remote db.

Symptoms: connections closing with little or no error message, requesting
ctid from a foreign table that doesn't have a ctid, even dataloss iin one
instance*.

Reproduce: create two Postgresql databases, a and b.  Create a foreign table
in a using a fdw other than postgres_fdw.  Multicorn is a good choice since
it is then easy to see everything.
Import the foreign table from a into b using postgres_fdw.  Run update
querries on the table in b.  Check the querries run against a.

Expected: Ideally postgres_fdw would check if the remote table is foreign
and if so, just pass the SQL through, or generate an error.

We don't actually need this functionality, but bugs in our system sometimes
cause a foreign table to be mapped to a foreign table.  When that happens,
it's hard to diagnose and causes unexpected results, include data loss in
one instance.*

*While postgres_fdw did cause data loss, we were able to retrieve it all
though Postrgres's point in time restore.  So overall, using Postgres was a
win for us in this case.




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

rbiro@interfacefinancial.com writes:
> Using postgres_fdw seems to assume that the foreign table is not also a
> foreign table in the remote db.

> Symptoms: connections closing with little or no error message, requesting
> ctid from a foreign table that doesn't have a ctid, even dataloss iin one
> instance*.

> Reproduce: create two Postgresql databases, a and b.  Create a foreign table
> in a using a fdw other than postgres_fdw.  Multicorn is a good choice since
> it is then easy to see everything.

Without a concrete test case, it's really hard to prove anything about
this, or even to tell which component the problem is in.  But a desultory
test using two levels of postgres_fdw seemed to work, which suggests that
you need to take this up with the multicorn authors.

contrib_regression=# create table base (a int, b text);
CREATE TABLE
contrib_regression=# create foreign table level1 (a int, b text)
server loopback options(schema_name 'public', table_name  'base');
CREATE FOREIGN TABLE
contrib_regression=# create foreign table level2 (a int, b text)
server loopback options(schema_name 'public', table_name  'level1');
CREATE FOREIGN TABLE
contrib_regression=# insert into level2 values(42, 'foo');
INSERT 0 1
contrib_regression=# select * from base;a  |  b  
----+-----42 | foo
(1 row)
contrib_regression=# update level2 set b = 'bar' where a = 42;
UPDATE 1
contrib_regression=# select * from base;a  |  b  
----+-----42 | bar
(1 row)
        regards, tom lane


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