Re: Improving RLS planning

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Dean Rasheed (dean(dot)a(dot)rasheed(at)gmail(dot)com) wrote:
>> Hmm. I've not read any of the new code yet, but the fact that this
>> test now reduces to a one-time filter makes it effectively useless as
>> a test of qual evaluation order because it has deduced that it doesn't
>> need to evaluate them. I would suggest replacing the qual with
>> something that can't be reduced, perhaps "2*a = 6".

> That's a good thought, I agree.

[ getting back to this patch finally... ] I made the suggested change
to that test case, and what I see is a whole lot of "NOTICE: snooped value
= whatever" outputs. The fact that there are none in the current test
output is because in

UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;

we currently decide that the subquery can't be flattened, but we then push
down the two leakproof quals into it, so that they get evaluated ahead of
the snoop() call. The revised code doesn't do that, allowing snoop() to
be called on rows that will fail the other two quals --- but AFAICS,
that's a feature not a bug. There is no security-based argument why
snoop() can't go before them, and on cost grounds it should.

I'd leave it as shown in the attached diff fragment, except that I'm
a bit worried about possible platform dependency of the output. The
hashing occurring in the subplans shouldn't affect output order, but
I'm not sure if we want a test output like this or not. Thoughts?

regards, tom lane

*************** SELECT * FROM v1 WHERE a=8;
*** 2114,2198 ****
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
! UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------------
! Update on public.t1 t1_4
! Update on public.t1 t1_4
! Update on public.t11 t1
! Update on public.t12 t1
! Update on public.t111 t1
! -> Subquery Scan on t1
Output: 100, t1.b, t1.c, t1.ctid
! Filter: snoop(t1.a)
! -> LockRows
! Output: t1_5.ctid, t1_5.a, t1_5.b, t1_5.c, t1_5.ctid, t12.ctid, t12.tableoid
! -> Nested Loop Semi Join
! Output: t1_5.ctid, t1_5.a, t1_5.b, t1_5.c, t1_5.ctid, t12.ctid, t12.tableoid
! -> Seq Scan on public.t1 t1_5
! Output: t1_5.ctid, t1_5.a, t1_5.b, t1_5.c
! Filter: ((t1_5.a > 5) AND (t1_5.a = 3) AND leakproof(t1_5.a))
! -> Append
! -> Seq Scan on public.t12
! Output: t12.ctid, t12.tableoid, t12.a
! Filter: (t12.a = 3)
! -> Seq Scan on public.t111
! Output: t111.ctid, t111.tableoid, t111.a
! Filter: (t111.a = 3)
! -> Subquery Scan on t1_1
! Output: 100, t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
! Filter: snoop(t1_1.a)
! -> LockRows
! Output: t11.ctid, t11.a, t11.b, t11.c, t11.d, t11.ctid, t12_1.ctid, t12_1.tableoid
! -> Nested Loop Semi Join
! Output: t11.ctid, t11.a, t11.b, t11.c, t11.d, t11.ctid, t12_1.ctid, t12_1.tableoid
! -> Seq Scan on public.t11
! Output: t11.ctid, t11.a, t11.b, t11.c, t11.d
! Filter: ((t11.a > 5) AND (t11.a = 3) AND leakproof(t11.a))
! -> Append
! -> Seq Scan on public.t12 t12_1
! Output: t12_1.ctid, t12_1.tableoid, t12_1.a
! Filter: (t12_1.a = 3)
! -> Seq Scan on public.t111 t111_1
! Output: t111_1.ctid, t111_1.tableoid, t111_1.a
! Filter: (t111_1.a = 3)
! -> Subquery Scan on t1_2
! Output: 100, t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
! Filter: snoop(t1_2.a)
! -> LockRows
! Output: t12_2.ctid, t12_2.a, t12_2.b, t12_2.c, t12_2.e, t12_2.ctid, t12_3.ctid, t12_3.tableoid
! -> Nested Loop Semi Join
! Output: t12_2.ctid, t12_2.a, t12_2.b, t12_2.c, t12_2.e, t12_2.ctid, t12_3.ctid, t12_3.tableoid
! -> Seq Scan on public.t12 t12_2
! Output: t12_2.ctid, t12_2.a, t12_2.b, t12_2.c, t12_2.e
! Filter: ((t12_2.a > 5) AND (t12_2.a = 3) AND leakproof(t12_2.a))
! -> Append
! -> Seq Scan on public.t12 t12_3
! Output: t12_3.ctid, t12_3.tableoid, t12_3.a
! Filter: (t12_3.a = 3)
! -> Seq Scan on public.t111 t111_2
! Output: t111_2.ctid, t111_2.tableoid, t111_2.a
! Filter: (t111_2.a = 3)
! -> Subquery Scan on t1_3
! Output: 100, t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
! Filter: snoop(t1_3.a)
! -> LockRows
! Output: t111_3.ctid, t111_3.a, t111_3.b, t111_3.c, t111_3.d, t111_3.e, t111_3.ctid, t12_4.ctid, t12_4.tableoid
! -> Nested Loop Semi Join
! Output: t111_3.ctid, t111_3.a, t111_3.b, t111_3.c, t111_3.d, t111_3.e, t111_3.ctid, t12_4.ctid, t12_4.tableoid
! -> Seq Scan on public.t111 t111_3
! Output: t111_3.ctid, t111_3.a, t111_3.b, t111_3.c, t111_3.d, t111_3.e
! Filter: ((t111_3.a > 5) AND (t111_3.a = 3) AND leakproof(t111_3.a))
! -> Append
! -> Seq Scan on public.t12 t12_4
! Output: t12_4.ctid, t12_4.tableoid, t12_4.a
! Filter: (t12_4.a = 3)
! -> Seq Scan on public.t111 t111_4
! Output: t111_4.ctid, t111_4.tableoid, t111_4.a
! Filter: (t111_4.a = 3)
! (73 rows)

! UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
a | b | c | d
---+---+---+---
--- 2096,2156 ----
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
! UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND 2*a = 6;
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------------------------------------
! Update on public.t1
! Update on public.t1
! Update on public.t11
! Update on public.t12
! Update on public.t111
! -> Seq Scan on public.t1
Output: 100, t1.b, t1.c, t1.ctid
! Filter: ((t1.a > 5) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND ((2 * t1.a) = 6) AND leakproof(t1.a))
! SubPlan 1
! -> Append
! -> Seq Scan on public.t12 t12_1
! Filter: (t12_1.a = t1.a)
! -> Seq Scan on public.t111 t111_1
! Filter: (t111_1.a = t1.a)
! SubPlan 2
! -> Append
! -> Seq Scan on public.t12 t12_2
! Output: t12_2.a
! -> Seq Scan on public.t111 t111_2
! Output: t111_2.a
! -> Seq Scan on public.t11
! Output: 100, t11.b, t11.c, t11.d, t11.ctid
! Filter: ((t11.a > 5) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t11.a) AND ((2 * t11.a) = 6) AND leakproof(t11.a))
! -> Seq Scan on public.t12
! Output: 100, t12.b, t12.c, t12.e, t12.ctid
! Filter: ((t12.a > 5) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t12.a) AND ((2 * t12.a) = 6) AND leakproof(t12.a))
! -> Seq Scan on public.t111
! Output: 100, t111.b, t111.c, t111.d, t111.e, t111.ctid
! Filter: ((t111.a > 5) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t111.a) AND ((2 * t111.a) = 6) AND leakproof(t111.a))
! (29 rows)

! UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND 2*a = 6;
! NOTICE: snooped value: 6
! NOTICE: snooped value: 7
! NOTICE: snooped value: 8
! NOTICE: snooped value: 9
! NOTICE: snooped value: 10
! NOTICE: snooped value: 6
! NOTICE: snooped value: 7
! NOTICE: snooped value: 8
! NOTICE: snooped value: 9
! NOTICE: snooped value: 10
! NOTICE: snooped value: 6
! NOTICE: snooped value: 7
! NOTICE: snooped value: 8
! NOTICE: snooped value: 9
! NOTICE: snooped value: 10
! NOTICE: snooped value: 6
! NOTICE: snooped value: 7
! NOTICE: snooped value: 8
! NOTICE: snooped value: 9
! NOTICE: snooped value: 10
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
a | b | c | d
---+---+---+---