sequence not restarting in a for loop (actually semi-random numbers)
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
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