From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan |
Date: | 2000-08-22 13:57:05 |
Message-ID: | 39A286B1.4653C17D@tm.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-08-22 14:04:41 | Re: Re: lost records --- problem identified! |
Previous Message | Michael Robinson | 2000-08-22 13:33:50 | Re: How Do You Pronounce "PostgreSQL"? |