Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan - Mailing list pgsql-hackers

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

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