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

Re: nextval skips values between consecutive calls

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: depstein(at)alliedtesting(dot)com, tgl(at)sss(dot)pgh(dot)pa(dot)us, pgsql-general(at)postgresql(dot)org, pgagarinov(at)alliedtesting(dot)com
Subject: Re: nextval skips values between consecutive calls
Date: 2011-10-29 05:53:00
Message-ID: 4EAB94BC.2040909@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-general
On 29/10/11 05:59, Merlin Moncure wrote:
> On Fri, Oct 28, 2011 at 11:32 AM,<depstein(at)alliedtesting(dot)com>  wrote:
>>> -----Original Message-----
>>> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
>>> Sent: Friday, October 28, 2011 8:29 PM
>>> To: Dmitry Epstein
>>> Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us; pgsql-general(at)postgresql(dot)org; Peter Gagarinov
>>> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>>>
>>> On Fri, Oct 28, 2011 at 10:28 AM,<depstein(at)alliedtesting(dot)com>  wrote:
>>>>> -----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.)
>>> Typically for guaranteed LATERAL-like behaviors you need to use a CTE.
>>>
>>> merlin
>> What's a CTE?
> with foo as (select generate_series(1, 1) ind)
> select nextval(' test_sequence'), ind from foo;
>
> merlin
>
CTE: Common Table Expression

as above
      WITH foo AS (...)
the temporary*_t_*able 'foo' is created once from the given 
*_e_*xpression, and is *_c_*ommon to the following select and any nested 
sub selects.

see '7.8. WITH Queries (Common Table Expressions)' in the manual for 9.1.1.


In response to

pgsql-general by date

Next:From: Arvind SinghDate: 2011-10-29 09:39:22
Subject: question related to pg_stat_database
Previous:From: Mohamed HashimDate: 2011-10-29 04:10:12
Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!

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