Date: Tue, 22 Jan 2008 12:27:54 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
To: silly_sad <sad@bankir.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: currval() within one statement
Message-ID: <20080122112753.GA13184@depesz.com>
Reply-To: depesz@depesz.com
References: <4795984E.7030904@bankir.ru>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <4795984E.7030904@bankir.ru>
User-Agent: Mutt/1.5.13 (2006-08-11)

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)