Thread: nextval skips values between consecutive calls

nextval skips values between consecutive calls

From

Date:

28 October 2011, 12:08:09

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.

Re: nextval skips values between consecutive calls

From

Tom Lane

Date:

28 October 2011, 12:21:50

<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

Re: nextval skips values between consecutive calls

From

Date:

28 October 2011, 12:30:21

> -----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.

Re: nextval skips values between consecutive calls

From

Merlin Moncure

Date:

28 October 2011, 13:29:30

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

Re: nextval skips values between consecutive calls

From

Date:

28 October 2011, 13:32:34

> -----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?

Re: nextval skips values between consecutive calls

From

Merlin Moncure

Date:

28 October 2011, 13:59:12

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

Re: nextval skips values between consecutive calls

From

Gavin Flower

Date:

29 October 2011, 17:26:15

On 29/10/11 05:59, Merlin Moncure wrote:

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

CTE: Common Table Expression

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.