From: Richard Huxton <dev@archonet.com>
To: sad <sad@bankir.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: currval() within one statement
Date: Tue, 22 Jan 2008 10:39:36 +0000
Message-ID: <4795C7E8.6030903@archonet.com> (raw)
In-Reply-To: <4795C3E8.9080607@bankir.ru>
References: <4795984E.7030904@bankir.ru>
<20080122073637.GB21676@a-kretschmer.de>
<4795A152.3080400@bankir.ru>
<4795B3D4.5020201@archonet.com>
<4795C3E8.9080607@bankir.ru>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, sad@bankir.ru
Subject: Re: currval() within one statement
In-Reply-To: <4795C7E8.6030903@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox