Unexplained behaviour

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(at)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(at)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(at)somemail(dot)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(at)thefreecat(dot)org
Independant Consultant and Developer
Delphi, Linux, Perl, PostgreSQL