Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D3CEB9F94A0 for ; Thu, 4 Oct 2007 12:19:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 98751-03 for ; Thu, 4 Oct 2007 12:19:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D538C9FB2B0 for ; Thu, 4 Oct 2007 12:19:45 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l94FIHi5021287; Thu, 4 Oct 2007 11:18:17 -0400 (EDT) To: Alvaro Herrera cc: Shane Ambler , PostgreSQL Hackers , Stephan Szabo , Jeff Frost , pgsql-sql@postgresql.org Subject: Re: [HACKERS] Why does the sequence skip a number with generate_series? In-reply-to: <20071004144656.GK6176@alvh.no-ip.org> References: <20071003175808.P16566@megazone.bigpanda.com> <4704F7CA.4070200@Sheeky.Biz> <20071004144656.GK6176@alvh.no-ip.org> Comments: In-reply-to Alvaro Herrera message dated "Thu, 04 Oct 2007 10:46:56 -0400" Date: Thu, 04 Oct 2007 11:18:16 -0400 Message-ID: <21286.1191511096@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/27 X-Sequence-Number: 29399 Alvaro Herrera 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