From: hubert depesz lubaczewski <depesz@depesz.com>
To: silly_sad <sad@bankir.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: currval() within one statement
Date: Tue, 22 Jan 2008 12:27:54 +0100
Message-ID: <20080122112753.GA13184@depesz.com> (raw)
In-Reply-To: <4795984E.7030904@bankir.ru>
References: <4795984E.7030904@bankir.ru>
On Tue, Jan 22, 2008 at 10:16:30AM +0300, silly_sad wrote:
> INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2;
> Where the trigger before insert on ttt is defined and this trigger calls
> nextval('ttt_id_seq').
> I was surprised having different values of currval() in ttt.a
> Is this the normal behavior ? Where is it described ?
currval is volatile function:
select provolatile from pg_proc where proname = 'currval';
it means it is called for every row separately.
and since it is inserted, it's evaluation is (i guess):
get 1 row from select
insert
get next row from select
insert
...
which means, that the sequence gets updated in mean time (by trigger).
if you want to have the same currval, i would suggest to do:
INSERT INTO ttt (a,b) SELECT (select currval('ttt_id_seq')), 'const' FROM ttt2;
(which should work).
or (and this would be definitely the best way) seriously rethink the
schema.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
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: depesz@depesz.com, sad@bankir.ru
Subject: Re: currval() within one statement
In-Reply-To: <20080122112753.GA13184@depesz.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