Odd behavior of updatable security barrier views on foreign tables

Поиск
Список
Период
Сортировка
От Etsuro Fujita
Тема Odd behavior of updatable security barrier views on foreign tables
Дата
Msg-id 54CB5B04.3060306@lab.ntt.co.jp
обсуждение исходный текст
Ответы Re: Odd behavior of updatable security barrier views on foreign tables  (Robert Haas <robertmhaas@gmail.com>)
Список pgsql-hackers
Hi,

I noticed that when updating security barrier views on foreign tables,
we fail to give FOR UPDATE to selection queries issued at ForeignScan.
Here is an example.

postgres=# create foreign table base_ftbl (person text, visibility text)
server loopback options (table_name 'base_tbl');
CREATE FOREIGN TABLE
postgres=# create view rw_view as select person from base_ftbl where
visibility = 'public';
CREATE VIEW
postgres=# explain verbose delete from rw_view;                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------Delete on
public.base_ftbl (cost=100.00..144.40 rows=14 width=6)  Remote SQL: DELETE FROM public.base_tbl WHERE ctid = $1  ->
ForeignScan on public.base_ftbl  (cost=100.00..144.40 rows=14
 
width=6)        Output: base_ftbl.ctid        Remote SQL: SELECT ctid FROM public.base_tbl WHERE ((visibility
= 'public'::text)) FOR UPDATE
(5 rows)

postgres=# alter view rw_view set (security_barrier = true);
ALTER VIEW
postgres=# explain verbose delete from rw_view;                                           QUERY PLAN
--------------------------------------------------------------------------------------------------Delete on
public.base_ftblbase_ftbl_1  (cost=100.00..144.54 rows=14
 
width=6)  Remote SQL: DELETE FROM public.base_tbl WHERE ctid = $1  ->  Subquery Scan on base_ftbl  (cost=100.00..144.54
rows=14width=6)        Output: base_ftbl.ctid        ->  Foreign Scan on public.base_ftbl base_ftbl_2
 
(cost=100.00..144.40 rows=14 width=6)              Output: base_ftbl_2.ctid              Remote SQL: SELECT ctid FROM
public.base_tblWHERE
 
((visibility = 'public'::text))
(7 rows)

Correct me if I am wrong.

Best regards,
Etsuro Fujita



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Heikki Linnakangas
Дата:
Сообщение: Re: Redesigning checkpoint_segments
Следующее
От: David Rowley
Дата:
Сообщение: Re: Performance improvement for joins where outer side is unique