Re: [v9.2] Fix Leaky View Problem

On Wed, Oct 19, 2011 at 6:35 AM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
> 2011/10/19 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Sun, Oct 16, 2011 at 4:46 AM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>>>> I tried to reproduce the scenario with enough small from/join_collapse_limit
>>>> (typically 1), but it allows to push down qualifiers into the least scan plan.
>>
>>> Hmm, you're right.  LIMIT 1000000000 prevents qual pushdown, but
>>> hitting from_collapse_limit/join_collapse_limit apparently doesn't.  I
>>> could have sworn I've seen this work the other way, but I guess not.
>>
>> No, the collapse_limit variables are entirely unrelated to subquery
>> flattening, or to qual pushdown for that matter.  They only restrict the
>> number of join paths we consider.  And we will attempt to push down
>> quals into an unflattened subquery, too, if it looks safe.  See
>> subquery_is_pushdown_safe, qual_is_pushdown_safe, etc in allpaths.c.
>>
> I tried to observe the behavior with a bit modification of is_simple_subquery
> that become to return 'false' always.
> (It is a simulation if and when a view with security_barrier would be given.)
>
> The expected behavior is to keep sub-query without flatten.
> However, the externally provided qualifiers are correctly pushed down.
>
> Do we need to focus on the code around above functions rather than
> distribute_qual_to_rels, to prevent undesirable pushing-down across
> security barrier?
>
> postgres=# CREATE VIEW v1 AS SELECT * FROM t1 WHERE a > 100;
> CREATE VIEW
> postgres=# CREATE VIEW v2 AS SELECT * FROM t2 JOIN t3 ON x = s;
> CREATE VIEW
> postgres=# EXPLAIN SELECT * FROM v1 WHERE b = 'bbb';
>                     QUERY PLAN
> ----------------------------------------------------
>  Seq Scan on t1  (cost=0.00..28.45 rows=2 width=36)
>   Filter: ((a > 100) AND (b = 'bbb'::text))
> (2 rows)
> postgres=# EXPLAIN SELECT * FROM v2 WHERE t = 'ttt';
>                           QUERY PLAN
> ----------------------------------------------------------------
>  Hash Join  (cost=25.45..52.73 rows=37 width=72)
>   Hash Cond: (t2.x = t3.s)
>   ->  Seq Scan on t2  (cost=0.00..22.30 rows=1230 width=36)
>   ->  Hash  (cost=25.38..25.38 rows=6 width=36)
>         ->  Seq Scan on t3  (cost=0.00..25.38 rows=6 width=36)
>               Filter: (t = 'ttt'::text)
> (6 rows)

Well, there's clearly some way to prevent pushdown from happening,
because sticking a LIMIT in there does the trick...

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