On Fri, Oct 28, 2011 at 11:32 AM, <depstein@xxxxxxxxxxxxxxxxx> wrote: >> -----Original Message----- >> From: Merlin Moncure [mailto:mmoncure@xxxxxxxxx] >> Sent: Friday, October 28, 2011 8:29 PM >> To: Dmitry Epstein >> Cc: tgl@xxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx; Peter Gagarinov >> Subject: Re: nextval skips values between consecutive calls >> >> On Fri, Oct 28, 2011 at 10:28 AM, <depstein@xxxxxxxxxxxxxxxxx> wrote: >> >> -----Original Message----- >> >> From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] >> >> Sent: Friday, October 28, 2011 7:22 PM >> >> To: Dmitry Epstein >> >> Cc: pgsql-general@xxxxxxxxxxxxxx; Peter Gagarinov >> >> Subject: Re: nextval skips values between consecutive calls >> >> >> >> <depstein@xxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
