pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcurrval() within one statement
11+ messages / 6 participants
[nested] [flat]
* currval() within one statement
@ 2008-01-22 07:16 silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 11:27 ` Re: currval() within one statement hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 11+ messages in thread
From: silly_sad @ 2008-01-22 07:16 UTC (permalink / raw)
To: pgsql-sql
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 ?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
@ 2008-01-22 07:36 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
1 sibling, 1 reply; 11+ messages in thread
From: A. Kretschmer @ 2008-01-22 07:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2008-01-22 07:54 ` sad <sad@bankir.ru>
2008-01-22 09:13 ` Re: currval() within one statement Guillaume Lelarge <guillaume@lelarge.info>
2008-01-22 09:13 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 11+ messages in thread
From: sad @ 2008-01-22 07:54 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
@ 2008-01-22 09:13 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-01-22 10:22 ` Re: currval() within one statement Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 1 reply; 11+ messages in thread
From: Guillaume Lelarge @ 2008-01-22 09:13 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
2008-01-22 09:13 ` Re: currval() within one statement Guillaume Lelarge <guillaume@lelarge.info>
@ 2008-01-22 10:22 ` Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 11+ messages in thread
From: Guillaume Lelarge @ 2008-01-22 10:22 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
@ 2008-01-22 09:13 ` Richard Huxton <dev@archonet.com>
2008-01-22 10:22 ` Re: currval() within one statement sad <sad@bankir.ru>
1 sibling, 1 reply; 11+ messages in thread
From: Richard Huxton @ 2008-01-22 09:13 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
2008-01-22 09:13 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
@ 2008-01-22 10:22 ` sad <sad@bankir.ru>
2008-01-22 10:39 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 11+ messages in thread
From: sad @ 2008-01-22 10:22 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
2008-01-22 09:13 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
2008-01-22 10:22 ` Re: currval() within one statement sad <sad@bankir.ru>
@ 2008-01-22 10:39 ` Richard Huxton <dev@archonet.com>
2008-01-22 11:27 ` Re: currval() within one statement silly_sad <sad@bankir.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Richard Huxton @ 2008-01-22 10:39 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
2008-01-22 09:13 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
2008-01-22 10:22 ` Re: currval() within one statement sad <sad@bankir.ru>
2008-01-22 10:39 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
@ 2008-01-22 11:27 ` silly_sad <sad@bankir.ru>
2008-01-22 12:18 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 11+ messages in thread
From: silly_sad @ 2008-01-22 11:27 UTC (permalink / raw)
To: pgsql-sql
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 ttt2 WHERE ....;
END;
P.S.
This happened because i am constantly trying to avoid procedural code
where possible to code SQL entirely.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` Re: currval() within one statement A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` Re: currval() within one statement sad <sad@bankir.ru>
2008-01-22 09:13 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
2008-01-22 10:22 ` Re: currval() within one statement sad <sad@bankir.ru>
2008-01-22 10:39 ` Re: currval() within one statement Richard Huxton <dev@archonet.com>
2008-01-22 11:27 ` Re: currval() within one statement silly_sad <sad@bankir.ru>
@ 2008-01-22 12:18 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2008-01-22 12:18 UTC (permalink / raw)
To: silly_sad <sad@bankir.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: currval() within one statement
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
@ 2008-01-22 11:27 ` hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 11+ messages in thread
From: hubert depesz lubaczewski @ 2008-01-22 11:27 UTC (permalink / raw)
To: silly_sad <sad@bankir.ru>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-01-22 12:18 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-22 07:16 currval() within one statement silly_sad <sad@bankir.ru>
2008-01-22 07:36 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-22 07:54 ` sad <sad@bankir.ru>
2008-01-22 09:13 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-01-22 10:22 ` Guillaume Lelarge <guillaume@lelarge.info>
2008-01-22 09:13 ` Richard Huxton <dev@archonet.com>
2008-01-22 10:22 ` sad <sad@bankir.ru>
2008-01-22 10:39 ` Richard Huxton <dev@archonet.com>
2008-01-22 11:27 ` silly_sad <sad@bankir.ru>
2008-01-22 12:18 ` Richard Huxton <dev@archonet.com>
2008-01-22 11:27 ` hubert depesz lubaczewski <depesz@depesz.com>
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