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

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

Are sequences in SQL standard at all ?

If they are, how are they used ?

> 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.

And if no triggers/rules use nextval('seq') ...

And it is also dependent on optimiser decisions, like order of scanning
the tuples - for seq being at 10 and sequval in 10,11,12,13,14
it can either update 1 or 5 tuples depending on the order of scanning the
tuples.

What I'm trying to say is that using currval/nextval in the same query is
inherently
undefined if we assume that currval means anything else than the value of
sequence
at the start of query

> 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".

What I would want is currval always return the value of sequence at the
start of current transaction.

If I need anything more complex I'd use pgplsql and save the value of
nextval()

I _don't_ want to use plpgsql for the simple case.

> 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.

That implies that I have to install plpgsql and probably also need to be
in transaction and also to use a function instead of query which is somewhat
painful to do interactively

> > 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).

I want curval to freeze the value at the beginning of query ;)

Other people may want it to do something else.

Could we add an additional function with strictly defined behaviour of
returning the value of a sequence at the beginning of current query, perhaps
called ccurval()

Would defining an additional function and marking it cacheable do the trick or
can such a function also return wrong data under some circumstances.

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