Unexplained behaviour - Mailing list pgsql-general

Hi,

I have a before-ins-row trigger that is supposed to find a unique
value for one of the fields but it seems sometimes there are
non-unique values that try to be created. They are rejected but I
don't see the bug.

50000 user accounts were created with this code but we seem to have a
few that fail and we don't understand how it's possible if concurrency is
not involved.

Hope someone can help...


Here's the trigger :

=====

drop function trig_identite_befinsrow();
create function trig_identite_befinsrow() returns opaque as '
declare n int4;
        n1 int4;
        n2 int4;
        i int4;
        c int4;
        s text;
begin
        -- create a unique 12 byte long field (IDMEMBRE) starting with '11' then
        -- 8 chars = minutes since 01/01/2000 then a 2-byte key with
        -- some silly algorithm of mine ;-)
        select reltime(now()-''january 1, 2000'')::int into n;
        n:=n/60;
        loop
                -- calculate key
                n1 := 0;
                s := ''11''||lpad(n,8,''0'');
                for i in 1..10 loop
                        if i=1     then n2:=2; end if;
                        if i=2  then n2:=3; end if;
                        if i=3  then n2:=5; end if;
                        if i=4  then n2:=7; end if;
                        if i=5  then n2:=11; end if;
                        if i=6  then n2:=13; end if;
                        if i=7  then n2:=17; end if;
                        if i=8  then n2:=19; end if;
                        if i=9  then n2:=23; end if;
                        if i=10 then n2:=29; end if;
                        n1:=n1::int+(substr(s,i,1)::int+1)::int*n2;
                end loop;
                n1:=n1 % 100;
                s:=s||lpad(n1,2,''0'');
                -- already exists ?
                select count(IDMEMBRE) into c from identite where IDMEMBRE=s;
                -- no, it's fine
                if c=0 then
                        new.idmembre:=s;
                        return new;
                        exit;
                end if;
                -- if we get here, the key already exists
                n := n-1;
        end loop;
        new.idmembre := s;
        return new;
end;
' language 'plpgsql';

drop trigger trig_identite_befinsrow on identite;
create trigger trig_identite_befinsrow before insert on identite for each row execute procedure
trig_identite_befinsrow();

=====

Now, the table's definition :

=====

mydb@miaou# \d identite
        Table "identite"
 Attribute |  Type   | Modifier
-----------+---------+----------
 idmembre  | text    | not null
 login     | text    |
 passe     | text    |
 actif     | char(1) |
 email     | text    |
 miles     | integer |
Indices: actif_identite_key,
         email_identite_key,
         identite_pkey,
         login_identite_key,
         miles_identite_key,
         passe_identite_key

mydb@miaou# \d identite_pkey
Index "identite_pkey"
 Attribute | Type
-----------+------
 idmembre  | text
unique btree (primary key)

=====

And here are the logs I get. I provide them as-is so that you can see
there does not seem to be 2 triggers running at the same time.
Unfortunately, there are other queries being executed at the same time
but they don't interfere (hope so).

NB: The first line is the insert command that triggers the trigger.

=====


query: insert into identite (login,passe,actif,email,miles) values ('someone','123456','O','someone@somemail.com',0)
ProcessQuery
query: SELECT  reltime(now()-'january 1, 2000')::int
StartTransactionCommand
query: select lower(urlpartenaire) from refererpartenaire where lower(urlpartenaire)=lower('perso.jobscout24.fr')
CommitTransactionCommand
ProcessQuery
CommitTransactionCommand
query: SELECT  $1 /60
StartTransactionCommand
query:  SELECT  idcondition
        FROM            conditions c,partenaires p
        WHERE           c.nompartenaire=p.nompartenaire
        AND                     p.nomcache='hjklfhgklsjg'
        AND                     c.nomcondition='macondition'
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query:  SELECT  m.OID
  FROM          cpt_maxibanner m
  WHERE         datebanner=date('now')
  AND           idcondition=1447
  AND                   membre='N'
ProcessQuery
StartTransactionCommand
query: select idmembre from identite where login='marseilliais'
ProcessQuery
CommitTransactionCommand
query: SELECT  0
query: SELECT  '11'||lpad( $1 ,8,'0')
query: SELECT  1
query: SELECT 10
query: SELECT   $1 =1
query: SELECT 2
query: SELECT   $1 =2
query: SELECT   $1 =3
query: SELECT   $1 =4
query: SELECT   $1 =5
query: SELECT   $1 =6
query: SELECT   $1 =7
query: SELECT   $1 =8
query: SELECT   $1 =9
query: SELECT   $1 =10
query: SELECT  $1 ::int+(substr( $2 , $3 ,1)::int+1)::int* $4
query: SELECT 3
query: SELECT 5
query: SELECT 7
query: SELECT 11
query: SELECT 13
query: SELECT 17
query: SELECT 19
query: SELECT 23
query: SELECT 29
query: SELECT  $1  % 100
query: SELECT  $1 ||lpad( $2 ,2,'0')
query: SELECT  count(IDMEMBRE) from identite where IDMEMBRE= $1
query: SELECT   $1 =0
query: SELECT   $1 -1
query: SELECT  $1
StartTransactionCommand
query: insert into prefs (idmembre,transinfopart,questionperso,reponseperso,fromsite,abonewsletter) values
('110065208528','O','','reum','yd3X4URQSdfG','O')
ProcessQuery
ERROR:  Cannot insert a duplicate key into unique index identite_pkey


--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Perl, PostgreSQL