postgres_fdw behaves oddly

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

Предыдущее
От: Fujii Masao
Дата:
Сообщение: Re: Immediate standby promotion
Следующее
От: Joel Jacobson
Дата:
Сообщение: Re: PL/pgSQL 2