Skip site navigation (1) Skip section navigation (2)

Re: currval() within one statement

From: Richard Huxton <dev(at)archonet(dot)com>
To: sad <sad(at)bankir(dot)ru>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: currval() within one statement
Date: 2008-01-22 10:39:36
Message-ID: 4795C7E8.6030903@archonet.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: silly_sadDate: 2008-01-22 11:27:29
Subject: Re: currval() within one statement
Previous:From: sadDate: 2008-01-22 10:22:32
Subject: Re: currval() within one statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group