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

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Tom Lane wrote:
>> The fact that some cases involving currval+nextval (but not all)

> Could you give me a good example of currval+nextval that has a
> SQL[92/99]-defined result, or even a predictable result?

currval & nextval aren't in the SQL standard, so asking for a standard-
defined result is rather pointless. However, it's certainly possible to
imagine cases where the result is predictable. For example,

UPDATE table SET dataval = foo, seqval = nextval('seq')
WHERE seqval = currval('seq')

is predictable if the seqval column is unique. Admittedly in that case
it wouldn't matter whether we pre-evaluated currval or not. But you'd
have to be very careful about what you mean by "pre-evaluation". For
example, the above could be executed many times within one interactive
query --- say, it could be executed inside a trigger function that's
fired multiple times by an interactive SELECT. Then the results will
change depending on just when you pre-evaluate currval. That's why I'd
rather leave it to the user to evaluate currval separately if he wants
pre-evaluation. That way the user can control what happens. If we
hard-wire an overly-optimistic pre-evaluation policy into the optimizer
then that policy will be wrong for some applications.

>> Especially not when there's a perfectly good way for you to make it do what you want...

> You mean marking it const in my personal copy of pgsql ? ;)

No, I meant putting a pre-evaluation into a plpgsql function, as I
illustrated earlier in this thread.

> Do you know of any circumstances where I would get _wrong_ answers by
> doing the above ?

I already told you earlier in this thread: it will fail inside sql or
plpgsql functions, because the optimizer will freeze the value of the
allegedly constant function sooner than you want, ie during first
execution of the sql/plpgsql function (assuming the input argument looks
like a constant, of course).

regards, tom lane