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

Hannu Krosing <hannu@tm.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