Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Why does the sequence skip a number withgenerate_series?

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>, Jeff Frost <jeff(at)frostconsultingllc(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: [HACKERS] Why does the sequence skip a number withgenerate_series?
Date: 2007-10-04 14:46:56
Message-ID: 20071004144656.GK6176@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Shane Ambler wrote:
> 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.

Don't use set-returning functions in "scalar context".  If you put them
in the FROM clause, as Stephan says above, it works fine.  Anywhere else
they have strange behavior and they are supported only because of
backwards compatibility.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to

Responses

pgsql-hackers by date

Next:From: Michael MeskesDate: 2007-10-04 14:47:50
Subject: Re: Not *quite* there on ecpg fixes
Previous:From: Tom LaneDate: 2007-10-04 14:43:16
Subject: Re: First steps with 8.3 and autovacuum launcher

pgsql-sql by date

Next:From: Tom LaneDate: 2007-10-04 15:18:16
Subject: Re: [HACKERS] Why does the sequence skip a number with generate_series?
Previous:From: Shane AmblerDate: 2007-10-04 14:25:14
Subject: Re: Why does the sequence skip a number with generate_series?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group