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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Shane Ambler <pgsql(at)Sheeky(dot)Biz>, 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 with generate_series?
Date: 2007-10-04 15:18:16
Message-ID: 21286.1191511096@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Alvaro Herrera <alvherre(at)commandprompt(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2007-10-04 15:33:06
Subject: Re: Not *quite* there on ecpg fixes
Previous:From: Simon RiggsDate: 2007-10-04 15:10:31
Subject: Re: First steps with 8.3 and autovacuum launcher

pgsql-sql by date

Next:From: Tore LukashaugenDate: 2007-10-04 17:39:09
Subject: What SQL is running against my DB?
Previous:From: Alvaro HerreraDate: 2007-10-04 14:46:56
Subject: Re: [HACKERS] Why does the sequence skip a number withgenerate_series?

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