Re: 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

Jean-Christophe Boggio <cat(at)thefreecat(dot)org> writes:
> 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.

Your trigger will obviously fail to generate unique keys if two
transactions are running concurrently, since any two transactions
started in the same minute will generate the same initial key,
and if they are running concurrently then neither will see the other's
entry in the table. Given that, I don't know why you're bothering.
Why don't you use a sequence object to generate the unique keys?

The fact that concurrency is not obvious from the log doesn't prove
a lot, since we can't see very much of the trigger's operation there.
(The internal queries of the trigger will appear in the log only when
compiled, ie, first time through that line in a particular backend.)
I think you should just assume that you're seeing failure in the
concurrency case.

regards, tom lane

Hi Tom,

Ref : Thursday, March 29, 2001 1:41:49 AM

TL> Your trigger will obviously fail to generate unique keys if two
TL> transactions are running concurrently, since any two transactions
TL> started in the same minute will generate the same initial key,
TL> and if they are running concurrently then neither will see the other's
TL> entry in the table. Given that, I don't know why you're bothering.
TL> Why don't you use a sequence object to generate the unique keys?

I thought I could not do something like : a table with sequence with
before-insert-trigger that would define a field based on the
already-assigned-sequence number.

I tried, it works perfect and no more errors.

Again, again, again, many thanks.

TL> (The internal queries of the trigger will appear in the log only when
TL> compiled, ie, first time through that line in a particular backend.)

Did not know that. Precious information !

--
Jean-Christophe Boggio
cat(at)thefreecat(dot)org
Independant Consultant and Developer
Delphi, Linux, Perl, PostgreSQL