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

Tom Lane wrote:
>
> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > Why is assuming a constant currval any more "invalid" than not doing so ?
>
> Because it's wrong: it changes the behavior from what happens if the
> optimizer does not do anything special with the function.

Optimizer already does "something special" regarding the function - it
decides the
order of execution of rows, and when both currval and nextval are
present it changes
the end result by doing so. If only currval is present currval is
constant.

But the case when "optimiser does not do anything with the function" is
completely
unpredictable in face of optimiser changing the order of things getting
scanned,
columns getting scanned and functions getting evaluated.

And I'm somewhat suspicious that we have any regression tests that are
dependent
of left-to-right or top-to-bottom execution of functions.

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

> yield unpredictable results is not an adequate argument for causing the
> behavior of other cases to change.

Are not all the other cases returning "undefined" (by the standard)
results ?

I mean that the fact that a seasoned pg coder has a feel for what will
happen
for some combination of nextval/currval for some combinations of indexes
and table
sizes does not make even his assumptions always right or future-proof.

> 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 ? ;)

I did

update pg_proc set proiscachable='t' where proname = 'currval';

And now it seems to do the right thing -

amphora2=# explain
amphora2-# select * from item where item_id = currval('item_id_seq');
NOTICE: QUERY PLAN:

Index Scan using item_pkey on item (cost=0.00..2.03 rows=1 width=140)

- Thanks.

Do you know of any circumstances where I would get _wrong_ answers by
doing the above ?
By wrong I mean really wrong, not just different from the case where
proiscachable='f'.

Can I now trust the optimiser to always pre-evalueate the currval() or
are there some
circumstances where the behaviour is still unpredictable ?

PS. I would not call plpgsql or temporary tables a perfectly good way ?
Plpgsql is not even installed by default (on linux at least).

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