1

I am new in postgres, created this trigger function to summarize a larger table to another table, I'm using pgAdmin when I run this in query tools, nothing will happen it don't give me an error or give me any successful message, what's wrong with it?

CREATE OR REPLACE FUNCTION insert_sum_usage() RETURNS trigger AS $insert_sum_usage$
DECLARE t timestamptz;
DECLARE u double precision;
BEGIN
t=(SELECT time FROM public.temp_time  where id=1 ); 
IF ( t  >  now() - interval '600 seconds')
THEN RETURN NEW;
ELSE
    u=(SELECT SUM(value)  FROM public.temp_usage WHERE labels[12] = 46  AND (labels[3] = 32 OR labels[3] = 62));
    INSERT INTO public.temp_sum_usage VALUES (NEW.time, u);
    UPDATE public.temp_time SET  time=NEW.time WHERE id=1;
RETURN NEW;
END IF;
END; 
$insert_sum_usage$ LANGUAGE plpgsql;

CREATE TRIGGER insert_sum_usage AFTER INSERT OR UPDATE ON public.temp_usage FOR EACH ROW EXECUTE PROCEDURE insert_sum_usage();
SMA
  • 157
  • 1
  • 1
  • 11
  • 2
    Stop and just *don't*. Data should be kept redundancy free to avoid the possibilities of inconsistencies. So writing sum over one table into another isn't a good thing to do. If you need the sum, you can always query it and get the *right* result. For convenience you can create a view that stores such a query so don't have to repeat it every time. – sticky bit Feb 10 '21 at 21:59
  • I see no problem with your trigger definition, other the conceptual one in the comment above. Bot I cannot follow the logic. Please describe your intention in more detail and describe how exactly your code misbehaves. – Laurenz Albe Feb 11 '21 at 01:28
  • I found the problem, this trigger takes lots of time to perform and inserting rate in main table is huge and it makes a huge queue and it made problem in the process, I should use cron to do so, @sticky bit you are right, my method was the worst choice – SMA Feb 11 '21 at 17:38

1 Answers1

0

It is hard to say, why this trigger do nothing. There are lot of constraints, and probably one constraint can be false. For this situation, the RAISE NOTICE statement is your best friend. You can see values of some variables or points where the process leaved the trigger:

IF ( t  >  now() - interval '600 seconds')
  RAISE NOTICE 'fast leaving (%, %)', t, now();
  THEN RETURN NEW;
ELSE
  ...

Just note: PLpgSQL is not C based language, so parenthesis around IF expressions are useless.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • I found the problem, this trigger takes lots of time to perform and inserting rate in main table is huge and it makes a huge queue and it made problem in the process, I should use cron to do so, thanks for replay ;-) – SMA Feb 11 '21 at 17:35