From: Shane Ambler <pgsql@Sheeky.Biz>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Why does the sequence skip a number with generate_series?
Date: Thu, 04 Oct 2007 23:55:14 +0930
Message-ID: <4704F7CA.4070200@Sheeky.Biz> (raw)
In-Reply-To: <20071003175808.P16566@megazone.bigpanda.com>
References: <Pine.LNX.4.64.0710021716490.4701@discord.home.frostconsultingllc.com>
<20071003175808.P16566@megazone.bigpanda.com>
Stephan Szabo wrote:
> On Tue, 2 Oct 2007, Jeff Frost wrote:
>
>> I expected these numbers to be in sync, but was suprised to see that the
>> sequence skips a values after every generate series.
>>
>> CREATE TABLE jefftest ( id serial, num int );
>> INSERT INTO jefftest (num) values (generate_series(1,10));
>> INSERT INTO jefftest (num) values (generate_series(11,20));
>> INSERT INTO jefftest (num) values (generate_series(21,30));
>
> It seems to do what you'd expect if you do
> INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a);
> INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a);
> INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a);
>
> I tried a function that raises a notice and called it as
> select f1(1), generate_series(1,10);
> and got 11 notices so it looks like there's some kind of phantom involved.
>
That's interesting - might need an answer from the core hackers.
I am posting this to pgsql-hackers to get their comments and feedback.
I wouldn't count it as a bug but it could be regarded as undesirable
side effects.
My guess is that what appears to happen is that the sequence is created
by incrementing as part of the insert steps and the test to check the
end of the sequence is -
if last_inserted_number > end_sequence_number
rollback_last_insert
This would explain the skip in sequence numbers.
My thoughts are that -
if last_inserted_number < end_sequence_number
insert_again
would be a better way to approach this. Of course you would also need to
check that the (last_insert + step_size) isn't greater than the
end_sequence_number when the step_size is given.
I haven't looked at the code so I don't know if that fits easily into
the flow of things.
The as foo(a) test would fit this as the sequence is generated into the
equivalent of a temporary table the same as a subselect, then used as
insert data. The rollback would be applied during the temporary table
generation so won't show when the data is copied across to fulfill the
insert.
Maybe the planner or the generate series function could use a temporary
table to give the same results as select from generate_series()
--
Shane Ambler
pgSQL@Sheeky.Biz
Get Sheeky @ http://Sheeky.Biz
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: pgsql@Sheeky.Biz, pgsql-hackers@postgresql.org, sszabo@megazone.bigpanda.com, jeff@frostconsultingllc.com
Subject: Re: Why does the sequence skip a number with generate_series?
In-Reply-To: <4704F7CA.4070200@Sheeky.Biz>
* 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