Thread: currval() within one statement
Helo
is it expected that the currval() changes its value between calls within
one statement ?
Look the following call:
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 ?
am Tue, dem 22.01.2008, um 10:16:30 +0300 mailte silly_sad folgendes:
> Helo
>
> is it expected that the currval() changes its value between calls within
> one statement ?
>
> Look the following call:
>
> INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2;
This fails if you never call nextval() for this sequence within this
session.
>
> Where the trigger before insert on ttt is defined and this trigger calls
> nextval('ttt_id_seq').
You don't need a TRIGGER. Just define your table with (a serial, ...)
and omit the column a if you INSERT a new row.
> I was surprised having different values of currval() in ttt.a
If you call nextval() befor the insert, then returns the currval(), for
instance, 5. If you call your insert with the TRIGGER like above, the
currval() returns this value 5, but your trigger fires and increase the
value. And, maybe, an other process has increased the sequence also.
Conclusion, don't call nextval() within a TRIGGER, and insert either
nextval() for the column or omit this column.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer wrote: >> is it expected that the currval() changes its value between calls within >> one statement ? > Conclusion, don't call nextval() within a TRIGGER, and insert either > nextval() for the column or omit this column. I only note that i still want to discuss the titled problem or to be given an exact pointer to documentation regarding the currval() behavior in the described situation, that i had.
sad wrote: > A. Kretschmer wrote: > >>> is it expected that the currval() changes its value between calls >>> within one statement ? > >> Conclusion, don't call nextval() within a TRIGGER, and insert either >> nextval() for the column or omit this column. > > I only note that i still want to discuss the titled problem or to be > given an exact pointer to documentation regarding the currval() behavior > in the described situation, that i had. > Well, your situation is weird, to say the least. currval() doesn't change the value of a sequence. Adding a trigger that calls nextval() will change the value. But you're not telling us which kind of trigger... per statement or for each row ? if it's for each row, then that's quite understandable. BTW, sequence functions are described here : http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
sad wrote: > A. Kretschmer wrote: > >>> is it expected that the currval() changes its value between calls >>> within one statement ? > >> Conclusion, don't call nextval() within a TRIGGER, and insert either >> nextval() for the column or omit this column. > > I only note that i still want to discuss the titled problem or to be > given an exact pointer to documentation regarding the currval() behavior > in the described situation, that i had. Well, the page in the docs isn't hard to find - http://www.postgresql.org/docs/8.2/static/functions-sequence.html But surely it works exactly as you would expect it to. nextval(S) advances the sequence and returns the new value currval(S) returns the current value of sequence S, which is whatever the previous call to nextval(S) returned. In the even you haven't called nextval(S) then it is undefined. What do you think should happen? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > sad wrote: >> A. Kretschmer wrote: >> >>>> is it expected that the currval() changes its value between calls >>>> within one statement ? >> >>> Conclusion, don't call nextval() within a TRIGGER, and insert either >>> nextval() for the column or omit this column. >> >> I only note that i still want to discuss the titled problem or to be >> given an exact pointer to documentation regarding the currval() behavior >> in the described situation, that i had. > > Well, the page in the docs isn't hard to find - > http://www.postgresql.org/docs/8.2/static/functions-sequence.html > > But surely it works exactly as you would expect it to. > > nextval(S) advances the sequence and returns the new value > > currval(S) returns the current value of sequence S, which is whatever > the previous call to nextval(S) returned. In the even you haven't called > nextval(S) then it is undefined. Then this is the question on the execution order of the statement INSERT...SELECT... > What do you think should happen? I had expected all the currval() calls to be called before all the triggers fired.
sad wrote: > Guillaume Lelarge wrote: >> sad wrote: >>> A. Kretschmer wrote: >>> >>>>> is it expected that the currval() changes its value between calls >>>>> within one statement ? >>> >>>> Conclusion, don't call nextval() within a TRIGGER, and insert either >>>> nextval() for the column or omit this column. >>> >>> I only note that i still want to discuss the titled problem or to be >>> given an exact pointer to documentation regarding the currval() behavior >>> in the described situation, that i had. >>> >> >> Well, your situation is weird, to say the least. currval() doesn't >> change the value of a sequence. Adding a trigger that calls nextval() >> will change the value. But you're not telling us which kind of >> trigger... per statement or for each row ? if it's for each row, then >> that's quite understandable. > > my fault -- i forgot to say. > The trigger is "for each row". > > It is understandable, i agree, but it is surprising -- alternative > behavior (constant result off currval()) is understandable too. > It isn't surprising. A "for each row" trigger will execute the trigger function for each individual row, which will executes nextval each time... so each currval will get a different value. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
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
Richard Huxton wrote: > 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. > > 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). It is clear. Thnx. > 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. That's why i'd post the question ! Trying to know if this behavior finally defined and documented. > > Can I ask what you were trying to achieve with the currval() select + > nextval() trigger combination. I've not seen that pattern before. > I'll try to describe... There is the global ttt_id_seq for the globally unique ids for all the tables. Since all those table are inherit from one ancestor. CREATE TABLE ttt1 ( id int primary key, info text); CREATE TABLE ttt ( id int primary key, a int references ttt1(id), info text); CREATE TABLE ttt2 ( id int primary key, info text); id default value is always set by the trigger before insert on each table for each row. The particular subproblem is to insert one record into ttt1 and then insert corresponding record into ttt, ___This is the place to use currval. using some data from a ttt2 ___This is a place to INSERT...SELECT... from ttt2; This works while SELECT FROM ttt2 returns exactly one row satisfying my needs. Finally it looks like: BEGIN; INSERT INTO ttt1 (....) VALUES (....); INSERT INTO ttt (a,info) SELECT currval('ttt_id_seq'), foo(info) FROM ttt2WHERE ....; END; P.S. This happened because i am constantly trying to avoid procedural code where possible to code SQL entirely.
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)
silly_sad wrote: > > id default value is always set by the trigger before insert on each > table for each row. > > The particular subproblem is to > insert one record into ttt1 > and then insert corresponding record into ttt, > ___This is the place to use currval. I'd be tempted to turn it the other way around and have an AFTER trigger that just uses NEW.id as the value to put into "ttt". The reason to use an AFTER trigger is that you know the value can't be changed at that point, whereas with a BEFORE trigger it might. In your case it doesn't matter, but it's probably a good idea to be consistent in these things. -- Richard Huxton Archonet Ltd