The following bug has been logged online:
Bug reference: 5290
Logged by: WildWezyr
Email address: wi.ld.we.zy.r@gmail.com
PostgreSQL version: 8.4.2
Operating system: Windows Vista
Description: Simple loop with insert into and check to avoid
duplicate values fails
Details:
This is simplified version of BUG #5289.
Given this one table:
create sequence spb_word_seq;
create table spb_word (
id bigint not null primary key default nextval('spb_word_seq'),
word varchar(410) not null unique
);
and these functions (first simulates generating words, seconds performs main
loop):
create sequence spb_wordnum_seq;
create or replace function spb_getWord() returns text as $$
declare
rn int;
letters varchar(255) := 'Ä
ÄÄÅÅóÅźżjklmnopqrstuvwxyz';
llen int := length(letters);
res text := '';
wordnum int;
begin
select nextval('spb_wordnum_seq') into wordnum;
rn := 3 * (wordnum + llen * llen * llen);
rn := (rn + llen) / (rn % llen + 1);
rn := rn % (rn / 2 + 10);
loop
res := res || substring(letters, rn % llen, 1);
rn := floor(rn / llen);
exit when rn = 0;
end loop;
return res;
end;
$$ language plpgsql;
create or replace function spb_runmeSimple2(cnt int) returns void as $$
declare
w varchar(410);
wordId int;
begin
perform setval('spb_wordnum_seq', 1, false);
truncate table spb_word cascade;
for i in 1 .. cnt loop
if i % 100 = 0 then raise notice 'i = %', i; end if;
select spb_getWord() into w;
select id into wordId from spb_word where word = w;
if wordId is null then
insert into spb_word (word) values (w);
end if;
end loop;
end;
$$ language plpgsql;
while executing select spb_runmeSimple2(10000000)
I run into sql error:
ERROR: duplicate key value violates unique constraint "spb_word_word_key"
CONTEXT: SQL statement "insert into spb_word (word) values ( $1 )"
PL/pgSQL function "spb_runmesimple2" line 15 at SQL statement
after unpredictable number of iterations - iteration number for which
execution will fail changes every time.
If I eliminate polish national chars from function spb_getWord i.e. it will
generate words with plain ascii chars there is no error and everything works
fine.