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

trigger problem (wrong results)

From: Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il>
To: pgsql-admin(at)postgresql(dot)org
Subject: trigger problem (wrong results)
Date: 2005-05-23 12:46:02
Message-ID: (view raw or flat)
Lists: pgsql-admin
Hi list!
I pretty newbie in using triggers ,so sorry if this is a known thing.
I was trying to count rows in several tables by one of its column for 
example i have :

          Table "schedule"
 Column    |         Type          | Modifiers 
 studentid    | numeric(9,0)          | 
 groupid      | numeric(10,0)         | 
Triggers: schedule_rashum_decrcounter_tr,

     Table "public.counter"
    Column    |         Type          | Modifiers 
 counter_type | character varying(30) |           --the name of the table we are counting
 ident        | numeric(10,0)         |           --a column from the target table
 count        | integer               | 

When a new student is inserted into the group i want to have the count for that group
to be increased, so i would increase the count in the row where ident = groupid and
If the row in the counter does not exists yet, i should create it.
Here is the trigger:

create trigger schedule_incrcount_tr after insert on schedule
for each row execute procedure incrcounter ('schedule');

create trigger schedule_decrcounter_tr  before delete on schedule
for each row execute procedure decrcounter ('schedule');

create or replace function incrcounter()  returns trigger as'
        input_refc              refcursor;
        qRes             record;
        q                text;
        ident            text;
        tableName        text;
        tableName :=     TG_ARGV[0];
        IF tableName = ''schedule'' THEN 
                ident :=  NEW.groupid;
        END IF;        
        q := ''SELECT * FROM counter WHERE counter_type= '' || '''''''' || tableName || '''''''' ||  '' and ident='' || ident ;

        OPEN input_refc FOR EXECUTE q;
        FETCH input_refc INTO  qRes;
        CLOSE input_refc;        

                EXECUTE ''UPDATE counter SET count=count+1 WHERE counter_type= '' || '''''''' || tableName || '''''''' ||  '' and ident='' || ident ;
                EXECUTE ''INSERT INTO counter (counter_type,ident,count) VALUES ( '' || '''''''' || tableName || '''''''' || '','' || ident || '','' || 1   || '' ) '' ;
        END IF;        
        RETURN NEW;
'LANGUAGE 'plpgsql' ;
The decrcounter is pretty the same except that in does counter=counter-1.
Obviously i have forgotten the 'for update' in the first select but those locks are done in the 
When trying the trigger all works fine but after using it on live application
i have some of the numbers wrong .The numbers are so terribly wrong that i can't explain it as a
transaction (concurrency) problem or something.
Please help ,what am i missing?
Many thanks .

pgsql-admin by date

Next:From: David BearDate: 2005-05-23 19:02:48
Subject: possible os recommendations
Previous:From: Magnus HaganderDate: 2005-05-22 17:47:55
Subject: Kerberos v4 users?

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