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

Re: nextval skips values between consecutive calls

From: <depstein(at)alliedtesting(dot)com>
To: <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>, <pgagarinov(at)alliedtesting(dot)com>
Subject: Re: nextval skips values between consecutive calls
Date: 2011-10-28 15:28:05
Message-ID: 29F36C7C98AB09499B1A209D48EAA615BEF34B248A@mail2a.alliedtesting.com (view raw or flat)
Thread:
Lists: pgsql-general
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, October 28, 2011 7:22 PM
> To: Dmitry Epstein
> Cc: pgsql-general(at)postgresql(dot)org; Peter Gagarinov
> Subject: Re: [GENERAL] nextval skips values between consecutive calls
> 
> <depstein(at)alliedtesting(dot)com> writes:
> > -- This is rather surprising
> > select nextval(' test_sequence'), generate_series(1, 1); -- 3, 1
> > select nextval(' test_sequence'), generate_series(1, 1); -- 5, 1
> 
> > Is there any explanation for why nextval skips a value in the second case?
> 
> The targetlist is evaluated twice because of the presence of the set-returning
> function.  On the second pass, generate_series reports that it's done, and so
> evaluation stops ... but nextval() was already called a second time.
> 
> SRFs in SELECT targetlists are a pretty dangerous thing, with a lot of surprising
> behaviors, especially if you combine them with other volatile functions.  I
> recommend avoiding them.  They'll probably be deprecated altogether as
> soon as we have LATERAL.
> 
> 			regards, tom lane

What's a good alternative in the meantime? Suppose I need to incorporate some unnests into my select, for example? (Well, I already found one alternative that seems to work, but I am not sure that's optimal.)

Dmitry Epstein | Developer
 
Allied Testing

www.alliedtesting.com
We Deliver Quality.

In response to

Responses

pgsql-general by date

Next:From: Thomas KellererDate: 2011-10-28 16:05:14
Subject: Re: "Named" column default expression
Previous:From: Tom LaneDate: 2011-10-28 15:21:40
Subject: Re: nextval skips values between consecutive calls

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