postgres_fdw behaves oddly

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: postgres_fdw behaves oddly
Date: 2014-09-01 11:15:39
Message-ID: 5404555B.5000407@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While working on [1], I've found that postgres_fdw behaves oddly:

postgres=# create foreign table ft (a int) server loopback options
(table_name 't');
CREATE FOREIGN TABLE
postgres=# select tableoid, * from ft;
tableoid | a
----------+---
16400 | 1
(1 row)

postgres=# select tableoid, * from ft where tableoid = 16400;
tableoid | a
----------+---
(0 rows)

I think that this is because (a) the qual that contains tableoid can be
sent to the remote as shown in the EXPLAIN output:

postgres=# explain verbose select tableoid, * from ft where tableoid =
16400;
QUERY PLAN
----------------------------------------------------------------------
Foreign Scan on public.ft (cost=100.00..193.20 rows=2560 width=8)
Output: tableoid, a
Remote SQL: SELECT a FROM public.t WHERE ((tableoid = 16400::oid))
Planning time: 0.110 ms
(4 rows)

and because (b) the tableoid value can be differs between the local and
the remote. I think that one simple way of fixing such issues would be
to consider unsafe to send to the remote a qual that contains any system
columns (though we should probably give special treatment to quals
containing only ctid). With the modification of postgres_fdw, we get
the right result:

postgres=# select tableoid, * from ft where tableoid = 16400;
tableoid | a
----------+---
16400 | 1
(1 row)

However, it's not complete enough. Here is another surising result
(note no tableoid column in the select list):

postgres=# select * from ft where tableoid = 16400;
a
---
(0 rows)

I think that this is because create_foreignscan_plan doesn't refer to
rel->baserestrictinfo when detecting whether any system columns are
requested. By the additional modification of create_foreignscan_plan,
we get the right result:

postgres=# select * from ft where tableoid = 16400;
a
---
1
(1 row)

I'd also like to propose to change the function so as to make reference
to rel->reltargetlist, not to attr_needed, to match the code with other
places. Please find attached a patch.

Thanks,

[1] https://commitfest.postgresql.org/action/patch_view?id=1386

Best regards,
Etsuro Fujita

Attachment Content-Type Size
fdw_sys_col.patch text/x-diff 2.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2014-09-01 11:30:21 Re: PL/pgSQL 2
Previous Message Fujii Masao 2014-09-01 11:14:41 Re: Immediate standby promotion