[BUGS] BUG #5290: Simple loop with insert into and check to avoid duplicate values fails

[WildWezyr]

The following bug has been logged online:

Bug reference:      5290
Logged by:          WildWezyr
Email address:      wi.ld.we.z...@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.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs