From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org >> PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Odd behavior of updatable security barrier views on foreign tables |
Date: | 2015-01-30 10:20:52 |
Message-ID: | 54CB5B04.3060306@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
-> Foreign Scan 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_ftbl base_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=14 width=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_tbl WHERE
((visibility = 'public'::text))
(7 rows)
Correct me if I am wrong.
Best regards,
Etsuro Fujita
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2015-01-30 11:37:19 | Re: Performance improvement for joins where outer side is unique |
Previous Message | Heikki Linnakangas | 2015-01-30 08:58:27 | Re: Redesigning checkpoint_segments |