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 |
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 |