Re: LIMITing number of results in a VIEW with global variables

On Oct14, 2011, at 16:43 , Thomas Girault wrote:
> CREATE OR REPLACE FUNCTION fuzzy2bool(FLOAT)
> RETURNS BOOLEAN LANGUAGE SQL AS 'SELECT set_mu($1);SELECT $1 > get_alpha()';

It seems dangerous for a cast to modify global state such a MU. The evaluation
order of functions during query execute isn't always easy to guess, and may
change depending on the execution plan.

> With this implicit cast, the query
> SELECT age, young(age) FROM set_alpha(0.1), employees WHERE young(age);
> is equivalent to
> SELECT age, young(age) FROM set_alpha(0.1), employees WHERE
> fuzzy2bool(young(age));

Those set_alpha() calls seem equally dangerous. If this alpha is supposed
to be a global parameter, why not set it *before* issuing the query?

> I can sort the results in the view 'sorted_employees' according to
> value MU of a fuzzy predicate thanks to fuzzy2bool cast function.
>
> CREATE OR REPLACE VIEW sorted_employees AS
> SELECT *, get_mu() as mu
> FROM employees
> ORDER BY mu DESC;

Are you aware that an ORDER BY clause in a VIEW is only going to work
if you do "SELECT .. .FROM view". If the outer query is more complex
than that, I wouldn't bet on the results coming back in the expected order.

Usually, you'd attach ORDER BY to the outermost query (or to subqueries
containing a LIMIT clause).

> The following query
> SELECT age, mu FROM set_k(5), set_alpha(0.1), filtered_employees

Again, you seem to rely on these set_k(), set_alpha() calls being
done before the query is executed. But postgres doesn't guarantee
that.

> WHERE young(age);
> gives the results :
> age mu
> 24 1
> 16 1
> instead of :
> age mu
> 16 1
> 21 0.899999976158142
> 24 0.600000023841858
> 26 0.400000005960464
> 26 0.400000005960464
>
> It seems that the 'LIMIT K' instruction have side effects on the MU value.

The execution plan may very well vary depending on the limit. Off-hand,
I'd guess that with a small K, one of these set_whatever() calls in one
of your FROM clauses gets executed after the computation it'd supposed
to affect has already happened.

> Why is it not working ? How to fix this issue ?

Don't rely on the execution order of function calls in a SELECT statement.
Divide your functions into two classes.

The ones which have side-effects (i.e. change global state). These should
*never* be called from SQL statements, except in the trivial case of
"SELECT my_func(...);". Also, they should be marked with VOLATILE

And the ones without side effects. Those should be marked with IMMUTABLE,
STABLE of VOLATILE, depending on how they're influenced by global state
changes. Read the documentation on these flags.

Finally, don't assume that ORDER BY inside a view influences the output
order of queries using the view. (Except in the trivial case of
"SELECT * FROM view"). As a rule of thumb, ORDER BY in a view only makes
sense if there's also a LIMIT clause. You are then guaranteed that the
view returns the first <limit> rows according to the specified order.
Don't assume they're necessarily returned in ascending order, though.

best regards,
Florian Pflug