BUG #5289: Unpredictable error in plpgsql function loop - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5289
Logged by:          WildWezyr
Email address:      wi.ld.we.zy.r@gmail.com
PostgreSQL version: 8.3.3, 8.4.2
Operating system:   Windows Vista
Description:        Unpredictable error in plpgsql function loop
Details:

For these tables:

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
);

create sequence spb_obj_word_seq;

create table spb_obj_word (
  id int not null primary key default nextval('spb_obj_word_seq'),
  doc_id int not null,
  idx int not null,
  word_id bigint not null references spb_word (id),
  constraint spb_ak_obj_word unique (doc_id, word_id, idx)
);

create sequence spb_word4obj_seq;

create table spb_word4obj (
  id int not null primary key default nextval('spb_word4obj_seq'),
  doc_id int not null,
  idx int not null,
  word varchar(410) not null,
  word_id bigint null references spb_word (id),
  constraint spb_ak_word4obj unique (doc_id, word_id, idx),
  constraint spb_ak_word4obj2 unique (doc_id, word, idx)
);

and these plpgsql functions:

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;

  --raise notice 'word for wordnum=% is %', wordnum, res;

  return res;
end;
$$ language plpgsql;



create or replace function spb_runme() returns void as $$
begin
  perform setval('spb_wordnum_seq', 1, false);
  truncate table spb_word4obj, spb_word, spb_obj_word;

  for j in 0 .. 50000-1 loop

    if j % 100 = 0 then raise notice 'j = %', j; end if;

    for i in 0 .. 20 - 1 loop
      insert into spb_word4obj (word, idx, doc_id) values (spb_getWord(), i,
j);
    end loop;

    update spb_word4obj set word_id = w.id from spb_word w
    where w.word = spb_word4obj.word and doc_id = j;

    insert into spb_word (word)
    select distinct word from spb_word4obj
    where word_id is null and doc_id = j;

    update spb_word4obj set word_id = w.id
    from spb_word w
    where w.word = spb_word4obj.word and
    word_id is null and doc_id = j;

    insert into spb_obj_word (word_id, idx, doc_id)
    select word_id, idx, doc_id from spb_word4obj where doc_id = j;
  end loop;
end;
$$ language plpgsql;

I get these errors:

first one:

NOTICE:  j = 8200
ERROR:  duplicate key value violates unique constraint "spb_word_word_key"
CONTEXT:  SQL statement "insert into spb_word (word) select distinct word
from spb_word4obj where word_id is null and doc_id =  $1 "
PL/pgSQL function "spb_runme" line 18 at SQL statement

second one:

NOTICE:  j = 500
ERROR:  null value in column "word_id" violates not-null constraint
CONTEXT:  SQL statement "insert into spb_obj_word (word_id, idx, doc_id)
select word_id, idx, doc_id from spb_word4obj where doc_id =  $1 "
PL/pgSQL function "spb_runme" line 27 at SQL statement

The errors occur in unpredictable manner - sometimes first one occurs, other
time second one occurs, but every time with different loop iteration number.
These errors refer to key duplication for unique constraint and null value
for non-null column. But my code just works to eliminate duplicates and null
values.

If I remove polish national characters from words generated by function
spb_getWord (i.e. letters varchar(255) :=   'abcdefghijklmnopqrstuvwxyz';) -
it runs without any errors, but with polish national chars (i.e. with
letters varchar(255) :=   'ąćęłńóśźżjklmnopqrstuvwxyz';) I get
strange errors described above.

My database was created with UTF8 encoding, I've tested it with Postgres
versions 8.3.3 and 8.4.2 on two machines (both Windows Vista boxes) and I
get the same unpredictable errors on both.