Re: BUG #14770: postgres_fdw assumes foreign table is in postgres

The second fdw cannot be postgres_fdw or everything will work as expected.
I thought the problem was in multicorn until I started getting requests for
a ctid column. Which hints at it being somewhere in postgres, most likely
the fdw. It's not important, but I thought you would like to know.

Ross

On Thu, Aug 3, 2017 at 11:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> rbiro(at)interfacefinancial(dot)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
>

--
*Ross Biro* | CTO
_______________________________________

O: 240-380-2231| F: 240-556-0361 <(240)%20556-0361>
The Interface Financial Group <https://interfacefinancial.com/>

CONFIDENTIALITY NOTICE: This email message, including any attachments, is
for the sole use of the intended recipient/s and may contain confidential &
privileged information. Any unauthorized review, use, disclosure, or
distribution is prohibited. If you are not the intended recipient, please
contact the sender by reply email and destroy all copies of the original
messages and any attachments.