Re: pl/pgsql enabled by default

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Hm. If you incorrectly mark your function as IMMUTABLE even though it
> > has side effects then the planner may indeed collapse this. Does the
> > planner know it can't collapse views if the underlying tables aren't
> > accessible to the user?
>
> There are no cases where function or view collapsing elides permissions
> checks (if you have a counterexample please provide it!!).

I'm talking about something like this. In guess there isn't a problem after
all but it depends critically on the AND short-circuiting (and the order of
evaluation of the expression not being changed).

db=> create view vtest as select * from test where a > 1 ;
db=> create or replace function f(integer) returns integer as 'begin raise notice ''foo %'', $1; return $1; end' language plpgsql;

db=> explain select * from vtest where f(a)>0;
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..27.50 rows=112 width=4)
Filter: ((a > 1) AND (f(a) > 0))
(2 rows)

I can't come up with any circumstances where the function will get called
before the a>1 clause. If it were indexed then it would be "evaluated" first
but it would no longer be relevant since the function wouldn't be getting
called.

But it's something to watch out for. If ever it seems like a wise idea to have
the optimizer fiddle with the order of evaluation, say based on the
selectivity or computational expense of the conditions then it could create a
problem.

--
greg