Re: Odd behavior of updatable security barrier views on foreign tables

On Fri, Jan 30, 2015 at 5:20 AM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 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.

That looks like a bug to me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company