Re: currval() within one statement

sad wrote:
> Richard Huxton wrote:
>
> Then this is the question on the execution order of the statement
> INSERT...SELECT...

You'll want "Overview of PostgreSQL internals" then

http://www.postgresql.org/docs/8.2/static/overview.html

>> What do you think should happen?
>
> I had expected all the currval() calls to be called before all the
> triggers fired.

If so, you'd get an error because nextval() wouldn't have been called
*at all* before currval() - unless you'd done so in the previous statement.

However, consider the case where your SELECT generated 100,000,000 rows
but had an unacceptable value in the second row. If you assembled the
result-set first then you'd have to store all those rows just to fail on
the second one.

In practice, I suspect it works this way because the planner / executor
arranges things in this manner for SELECT statements (so you can e.g.
stop early with a LIMIT clause).

However, relying on a specific order of execution (unless it's defined
in the SQL standard somewhere) is probably unwise. A future optimisation
might make your assumptions wrong.

Can I ask what you were trying to achieve with the currval() select +
nextval() trigger combination. I've not seen that pattern before.

--
Richard Huxton
Archonet Ltd