pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
To: pgsql-admin@postgresql.org
Subject: trigger problem (wrong results)
Date: Mon, 23 May 2005 15:46:02 +0300
Message-ID: <4291D08A.7000007@mail.jct.ac.il> (raw)


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.




Message-ID: <4291D08A.7000007@mail.jct.ac.il>
Permalink:  ../4291D08A.7000007@mail.jct.ac.il/
Also on:    postgresql.org/message-id/4291D08A.7000007@mail.jct.ac.il

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tsurkin@mail.jct.ac.il
  Subject: Re: trigger problem (wrong results)
  In-Reply-To: <4291D08A.7000007@mail.jct.ac.il>
  References: <4291D08A.7000007@mail.jct.ac.il>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox