postgres_fdw behaves oddly
От | Etsuro Fujita |
---|---|
Тема | postgres_fdw behaves oddly |
Дата | |
Msg-id | 5404555B.5000407@lab.ntt.co.jp обсуждение исходный текст |
Ответы |
Re: postgres_fdw behaves oddly
|
Список | 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
Вложения
В списке pgsql-hackers по дате отправления: