Skip site navigation (1) Skip section navigation (2)

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

From: "WildWezyr" <wi(dot)ld(dot)we(dot)zy(dot)r(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5290: Simple loop with insert into and check to avoid duplicate values fails
Date: 2010-01-19 17:07:45
Message-ID: 201001191707.o0JH7jsd098806@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5290
Logged by:          WildWezyr
Email address:      wi(dot)ld(dot)we(dot)zy(dot)r(at)gmail(dot)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.

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-01-19 17:15:15
Subject: Re: BUG #5288: Restoring a 7.4.5 -Fc dump using -j 2 segfaults (patch included)
Previous:From: Kevin GrittnerDate: 2010-01-19 15:37:14
Subject: Re: BUG #5284: Postgres CPU 100% and worker took too long to start; cancelled... Systemdown

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group