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

Tom Lane wrote:
>
> 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...

IMHO, if semantics in undefined then altering it should be OK, no?

What I mean is that there is no safe use of nextval and currval in the
same sql sentence, even if it is used automatically, as in "DEFAULT
NEXTVAL('S')"
and thus marking it as constant is as correct as not marking it, only
more predictable.

And predictability is GOOD ;)

I would even suggest that PG would warn about or even refuse to run
queries
that have both nextval and curval of the same sequence inside them
(and pre-evaluate nextval) as only that case has _any_ predictability.

------------
Hannu