Thread: sequence not restarting in a for loop (actually semi-random numbers)
sequence not restarting in a for loop (actually semi-random numbers)
From
Ivan Sergio Borgonovo
Date:
I've a function that does something like: create temp sequence tmp_seq; for _row in (select id from mylist where Weight<_Weight) loop alter sequence tmp_seq restart with 1; -- perform setval('tmp_seq',1,false); insert into mytop (id,n) select id, nextval('tmp_seq') from biglist join mylist on biglist.id=mylist order by biglist.something limit 3; end loop; drop sequence tmp_seq; I'd expect something like 1,1 1,2 1,3 127,1 127,2 127,3 but I get something like 1,5 1,6 1,7 127,55 127,56 127,57 152,6 152,7 152,8 what should I do to obtain the result I was expecting? -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > insert into mytop (id,n) select id, nextval('tmp_seq') > from biglist join mylist on biglist.id=mylist > order by biglist.something limit 3; I suspect you are expecting the nextval()s to be done after the LIMIT, but actually they'll most likely be done before it, ie you're getting a lot of unintended nextvals happening. Try doing the ORDER BY/LIMIT in a sub-select. regards, tom lane
Re: sequence not restarting in a for loop (actually semi-random numbers)
From
Ivan Sergio Borgonovo
Date:
On Wed, 12 Nov 2008 11:17:03 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > insert into mytop (id,n) select id, nextval('tmp_seq') > > from biglist join mylist on biglist.id=mylist > > order by biglist.something limit 3; > > I suspect you are expecting the nextval()s to be done after the > LIMIT, but actually they'll most likely be done before it, ie > you're getting a lot of unintended nextvals happening. Try doing > the ORDER BY/LIMIT in a sub-select. Thanks. That did it. I think I've seen similar replies before... but I couldn't relate them to my problem. I'd do like Bart Simpson: order by, limit, nextval, sequence, order by, limit, nextval, sequence... so that this answer will be well indexed ;) -- Ivan Sergio Borgonovo http://www.webthatworks.it