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, 1Is 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, 1Is 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 callsOn 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 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 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 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 callsOn 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 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 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 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:
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 callsOn 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 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 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.