title: Why this code fails in PostgreSQL and how to fix it (work-around)? Is it Postgres SQL engine flaw?
body: <p>I've been working on text parsing task when I found strange Postgres behavior. My original code exposing strange error was written in Java with JDBC connectivity for PostgreSQL (v8.3.3 and v8.4.2 tested), here is my original post: <a href="https://stackoverflow.com/questions/2086653/is-it-an-error-of-postgresql-sql-engine-and-how-to-avoid-workaround-it/2089403#2089403">Is it an error of PostgreSQL SQL engine and how to avoid (workaround) it?</a>. I've just ported my Java code given there to pure plpgsql and it gives the same errors (same behavior as described in original post).</p>
  
  <p>Simplified code has now nothing to do with parsing - it just generates pseudo-random (but repeatable) words and inserts them after normalizing (table <code>spb_word</code> holds unique words and ids, they are referenced by id in final table <code>spb_obj_word</code> and table <code>spb_word4obj</code> works as input buffer).</p>
  
  <p>Here are my tables (c&amp;p from OP):</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>and code ported to plpgsql from original Java code:</p>
  
  <pre><code>create sequence spb_wordnum_seq;
  
  create or replace function spb_getWord() returns text as $$
  declare
    rn int;
    letters varchar(255) :=   'ąćęłńóśźżjklmnopqrstuvwxyz';
                            --'abcdefghijklmnopqrstuvwxyz';
    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;
  
      delete from spb_word4obj where doc_id = j;
  
      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;
  </code></pre>
  
  <p>To run this simply execute <code>select spb_runme()</code> as SQL statement.</p>
  
  <p>Here is first example of error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and second one:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>These errors occur in unpredictable manner - every time in different iteration (<code>j</code>) and with different words causing error.</p>
  
  <p>When polish national characters (<code>ąćęłńóśźż</code>) are eliminated from generated words (line <code>letters varchar(255) :=   'ąćęłńóśźżjklmnopqrstuvwxyz';</code> becomes <code>letters varchar(255) :=   'abcdefghijklmnopqrstuvwxyz';</code>) there is no error! My DB is created with UTF-8 encoding, so there should be no problem with non-ascii chars, but apparently it is very important!</p>
  
  <p><strong>Now my question: what is wrong with my code? Or is it something serious wrong with PostgreSQL? How to work around this error?</strong></p>
  
  <p>BTW: If it is an error in PostgreSQL engine, then how this DB can be trustworthy? Should I move to one of free alternatives (e.g. MySQL)?</p>
  
  <hr>
  
  <p><strong>UPDATE:</strong> <em>extra explanations (mainly for OMG Ponies)</em></p>
  
  <p>If I remove unnecessary <code>delete</code> - I still have the same errors.</p>
  
  <p>Function <code>spb_getWord()</code> must generate words with duplicates - it simulates text parsing and dividing it to words - and some words get repeated - that is normal and rest of my code is dealing with duplicates. Because of possible duplicates generated by <code>spb_getWord()</code> I insert words to buffer table <code>spb_word4obj</code> and then I update <code>word_id</code> in this table for already processed words from <code>spb_word</code>. So now - if row in <code>spb_word4obj</code> has <code>word_id</code> not null - then it is a duplicate, so I will not insert this word into <code>spb_word</code>. But - as OMG Ponies mentioned, I get error <code>duplicate key value violates unique constraint</code> which means my code that handles duplicates properly fails. I.e. my code fails because of internal Postgres error - proper code is somehow badly executed by Postgres and fails.</p>
  
  <p>After inserting new words (duplicates recognized and marked not to be inserted) into <code>spb_word</code> my code finally inserts normalized words into <code>spb_obj_word</code> - replacing word body with reference to unduplicated entry in <code>spb_word</code>, but this again fails sometimes because of Postgres internal error. Again I think my code is proper but it fail because there is problem in Postgres SQL engine itself.</p>
  
  <p>Adding or removing polish national letters from generated words by <code>spb_getWord</code> only assures me that it is strange Postgres error - all unique/duplicates considerations remain the same but allowing/disallowing some letters from words leads to errors or eliminates them. So it is not the case of error in my code - unproper handling of duplicates.</p>
  
  <p>Second thing that assures me that it is not error in my code is unpredictable moment of errors that are detected. Every run of my code does the same sequence of words so it should always break in the same place with the same value causing the error. But it is not - it is quite random moment that it fails.</p>