Robert Haas <robertmhaas@gmail.com> writes:
> Normally this kind of reordering doesn't actually affect the result of
> the query because normally the quals that are being evaluated don't
> have any side-effects, but in your query you've chosen something that
> can throw an exception, so it's user-visible.
There's a pretty explicit disclaimer here:
http://www.postgresql.org/docs/8.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
that evaluation order is not guaranteed for WHERE clauses. While we
could try to tighten that up, I'll bet quite a lot that the howls of
pain from people whose queries stopped getting optimized would drown
out anyone who thought it was an improvement.
In this particular case I think you could make it better by putting the
unsafe function call in a subselect's output list, and placing the
restrictive condition in a WHERE clause above the subselect. However,
that's only safe because we have restrictions forbidding folding of
subselects whose output lists include volatile functions. The problem
here is *not* that pg_relation_size is volatile in the normal sense,
it's the possibility of throwing error on its input; so the fact that
the problem goes away seems a bit accidental. I don't know quite what
we might do about that. Marking functions as "could throw error"
doesn't seem especially helpful, because the set of functions not so
markable is durn close to empty.
regards, tom lane