Odd behavior of updatable security barrier views on foreign tables

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

Responses

Browse pgsql-hackers by date

  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