Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan

Don Baccus <dhogaza(at)pacifier(dot)com> writes:
> Does Postgres guarantee order of execution of functions?

No, and I don't recall having seen anything about it in the SQL spec
either. If you were doing something like

select foo, nextval('seq') from tab where bar < currval('seq')

then there's no issue of "order of evaluation" per se: nextval will be
evaluated at just those rows where the WHERE clause has already
succeeded. However, the results would still depend on the order in
which tuples are scanned, an order which is most definitely not
guaranteed by the spec nor by our implementation. (Also, in a
pipelined implementation it's conceivable that the WHERE clause would
get evaluated for additional tuples before nextval has been evaluated
at a matching tuple.)

However, that just shows that some patterns of usage of the function
will yield unpredictable results. I don't think that translates to an
argument that the optimizer is allowed to make semantics-altering
transformations...

regards, tom lane