From: | Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Noah Misch <noah(at)leadboat(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Kohei(dot)Kaigai(at)emea(dot)nec(dot)com, thom(at)linux(dot)com, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [v9.2] Fix Leaky View Problem |
Date: | 2011-10-19 10:35:49 |
Message-ID: | CADyhKSUPFQDX4f4mtyNmFk6TpjUz+8=zkHFD5otNZ_+cRFw3kg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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)
Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2011-10-19 10:36:08 | Re: Separating bgwriter and checkpointer |
Previous Message | Kohei KaiGai | 2011-10-19 10:18:34 | Re: [v9.2] Object access hooks with arguments support (v1) |