From: Shane Ambler <pgsql@Sheeky.Biz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [SQL] Why does the sequence skip a number with generate_series?
Date: Sat, 06 Oct 2007 10:20:00 +0930
Message-ID: <4706DBB8.8010000@Sheeky.Biz> (raw)
In-Reply-To: <21286.1191511096@sss.pgh.pa.us>
References: <Pine.LNX.4.64.0710021716490.4701@discord.home.frostconsultingllc.com>
<20071003175808.P16566@megazone.bigpanda.com>
<4704F7CA.4070200@Sheeky.Biz>
<20071004144656.GK6176@alvh.no-ip.org>
<21286.1191511096@sss.pgh.pa.us>
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Shane Ambler wrote:
>>>> 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));
>
>> Don't use set-returning functions in "scalar context".
>
> I think what is actually happening is that the expanded targetlist is
>
> nextval('seq'), generate_series(1,10)
>
> On the eleventh iteration, generate_series() returns ExprEndResult to
> show that it's done ... but the 11th nextval() call already happened.
> If you switched the columns around, you wouldn't get the extra call.
>
> If you think that's bad, the behavior with multiple set-returning
> functions in the same targetlist is even stranger. The whole thing
> is a mess and certainly not something we would've invented if we
> hadn't inherited it from Berkeley.
>
> regards, tom lane
Would a re-write be something worth adding to the todo list?
and/or maybe add something about this to the manual?
--
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, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [SQL] Why does the sequence skip a number with generate_series?
In-Reply-To: <4706DBB8.8010000@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