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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
