Обсуждение: nextval skips values between consecutive calls
I just found an odd thing about nextval (PostgreSQL 9.0): When nextval is called together with a function returning a
sequence,such as generate_series or unnest, it skips one value between consecutive calls:
create sequence test_sequence;
-- This works as expected
select nextval(' test_sequence'); -- 1
select nextval(' test_sequence'); -- 2
-- This is rather surprising
select nextval(' test_sequence'), generate_series(1, 1); -- 3, 1
select nextval(' test_sequence'), generate_series(1, 1); -- 5, 1
drop sequence test_sequence;
Is there any explanation for why nextval skips a value in the second case?
By the way, if the second query is rewritten as follows, nextval again generates consecutive values:
select nextval(' test_sequence'), ind
from (select generate_series(1, 1) ind) A;
Is this a bug or a feature?
Dmitry Epstein | Developer
Allied Testing
www.alliedtesting.com
We Deliver Quality.
<depstein@alliedtesting.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
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, October 28, 2011 7:22 PM
> To: Dmitry Epstein
> Cc: pgsql-general@postgresql.org; Peter Gagarinov
> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>
> <depstein@alliedtesting.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.
On Fri, Oct 28, 2011 at 10:28 AM, <depstein@alliedtesting.com> wrote:
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Sent: Friday, October 28, 2011 7:22 PM
>> To: Dmitry Epstein
>> Cc: pgsql-general@postgresql.org; Peter Gagarinov
>> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>>
>> <depstein@alliedtesting.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
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure@gmail.com]
> Sent: Friday, October 28, 2011 8:29 PM
> To: Dmitry Epstein
> Cc: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org; Peter Gagarinov
> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>
> On Fri, Oct 28, 2011 at 10:28 AM, <depstein@alliedtesting.com> wrote:
> >> -----Original Message-----
> >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> >> Sent: Friday, October 28, 2011 7:22 PM
> >> To: Dmitry Epstein
> >> Cc: pgsql-general@postgresql.org; Peter Gagarinov
> >> Subject: Re: [GENERAL] nextval skips values between consecutive calls
> >>
> >> <depstein@alliedtesting.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?
On Fri, Oct 28, 2011 at 11:32 AM, <depstein@alliedtesting.com> wrote:
>> -----Original Message-----
>> From: Merlin Moncure [mailto:mmoncure@gmail.com]
>> Sent: Friday, October 28, 2011 8:29 PM
>> To: Dmitry Epstein
>> Cc: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org; Peter Gagarinov
>> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>>
>> On Fri, Oct 28, 2011 at 10:28 AM, <depstein@alliedtesting.com> wrote:
>> >> -----Original Message-----
>> >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> >> Sent: Friday, October 28, 2011 7:22 PM
>> >> To: Dmitry Epstein
>> >> Cc: pgsql-general@postgresql.org; Peter Gagarinov
>> >> Subject: Re: [GENERAL] nextval skips values between consecutive calls
>> >>
>> >> <depstein@alliedtesting.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
On 29/10/11 05:59, Merlin Moncure wrote:
as above
WITH foo AS (...)
the temporary table 'foo' is created once from the given expression, and is common to the following select and any nested sub selects.
see '7.8. WITH Queries (Common Table Expressions)' in the manual for 9.1.1.
CTE: Common Table ExpressionOn Fri, Oct 28, 2011 at 11:32 AM, <depstein@alliedtesting.com> wrote:-----Original Message----- From: Merlin Moncure [mailto:mmoncure@gmail.com] Sent: Friday, October 28, 2011 8:29 PM To: Dmitry Epstein Cc: tgl@sss.pgh.pa.us; pgsql-general@postgresql.org; Peter Gagarinov Subject: Re: [GENERAL] nextval skips values between consecutive calls On Fri, Oct 28, 2011 at 10:28 AM, <depstein@alliedtesting.com> wrote:-----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Friday, October 28, 2011 7:22 PM To: Dmitry Epstein Cc: pgsql-general@postgresql.org; Peter Gagarinov Subject: Re: [GENERAL] nextval skips values between consecutive calls <depstein@alliedtesting.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, 1Is there any explanation for why nextval skips a value in the secondcase?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 asecond 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 laneWhat'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. merlinWhat's a CTE?with foo as (select generate_series(1, 1) ind) select nextval(' test_sequence'), ind from foo; merlin
as above
WITH foo AS (...)
the temporary table 'foo' is created once from the given expression, and is common to the following select and any nested sub selects.
see '7.8. WITH Queries (Common Table Expressions)' in the manual for 9.1.1.