X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5412352849 for ; Mon, 23 May 2005 09:43:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61501-07 for ; Mon, 23 May 2005 12:43:15 +0000 (GMT) Received: from mail2.cc.huji.ac.il (real-outmail.cc.huji.ac.il [132.64.1.18]) by svr1.postgresql.org (Postfix) with ESMTP id AF41952879 for ; Mon, 23 May 2005 09:43:12 -0300 (ADT) Received: from mail2.cc.huji.ac.il (localhost [127.0.0.1]) by mail2.cc.huji.ac.il (Postfix) with SMTP id 835FE18DB33; Mon, 23 May 2005 15:44:00 +0300 (IDT) Received: by mail2.cc.huji.ac.il (Postfix, from userid 31998) id 7B21F18DB65; Mon, 23 May 2005 15:44:00 +0300 (IDT) Received: from [132.64.52.127] (evegeni.mm.huji.ac.il [132.64.52.127]) by mail2.cc.huji.ac.il (Postfix) with ESMTP id E3FC118DB33 for ; Mon, 23 May 2005 15:43:59 +0300 (IDT) Message-ID: <4291D08A.7000007@mail.jct.ac.il> Date: Mon, 23 May 2005 15:46:02 +0300 From: Tsirkin Evgeny User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: trigger problem (wrong results) Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus: scanned by VAMS 2005 version 8.728 from Sun May 22 18:12:37 2005 CEST X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/266 X-Sequence-Number: 17695 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, schedule_rashum_incrcount_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 counter_type='schedule'. 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' DECLARE input_refc refcursor; qRes record; q text; ident text; tableName text; BEGIN 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; IF FOUND THEN EXECUTE ''UPDATE counter SET count=count+1 WHERE counter_type= '' || '''''''' || tableName || '''''''' || '' and ident='' || ident ; ELSE EXECUTE ''INSERT INTO counter (counter_type,ident,count) VALUES ( '' || '''''''' || tableName || '''''''' || '','' || ident || '','' || 1 || '' ) '' ; END IF; RETURN NEW; END; '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 application. 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 . Evgeny.