Broken database trigger in postgresql

[Burma]

I have a database with a bunch of insert triggers (5) that fire pg_notify events.

These have all been working happily for about a year until 12:00 today where one of them seems to have come to a grinding hault.

I'm struggling to debug the issue.

The notification is picked up by a queue in rabbitMQ and my clients listen to that. As I said its been working fine for ages now, but went live last week and of course broke today.

I can't see the data hitting rabbitMQ, but at this stage can't work out if its a problem with the trigger not publishing the notification, or a problem with rabbit.

I can't find anything that tells me if the trigger has fired.

The code for the trigger is this

create or replace function public.notify_predl()
returns trigger
language plpgsql
as $function$
declare
my_record record;
begin
select 
            to_char(NEW.datetime, 'YYYY-MM-DD HH24:MI:SS') as datetime,
            NEW.duid, 
            NEW.agcstatus,  
            NEW.availability, 
            NEW.connectionpointid,
            NEW.dispatchmode,
            NEW.initialmw,
            NEW.intervention,
            to_char(NEW.lastchanged, 'YYYY-MM-DD HH24:MI:SS') as lastchanged,
            NEW.lower5min,
            NEW.lower5minactualavailability,
            NEW.lower5minflags, 
            NEW.lower60sec,
            NEW.lower60secactualavailability,
            NEW.lower60secflags,
            NEW.lower6sec, 
            NEW.lower6secactualavailability,
            NEW.lower6secflags,
            NEW.lowerreg, 
            NEW.lowerregactualavailability,
            NEW.lowerregflags,
            NEW.periodid,
            NEW.predispatchseqno,
            NEW.raise5min,
            NEW.raise5minactualavailability,
            NEW.raise5minflags, 
            NEW.raise60sec,
            NEW.raise60secactualavailability,
            NEW.raise60secflags,
            NEW.raise6sec, 
            NEW.raise6secactualavailability,
            NEW.raise6secflags,
            NEW.raisereg, 
            NEW.raiseregactualavailability,
            NEW.raiseregflags,
            NEW.rampdownrate, 
            NEW.rampuprate,
            NEW.runno,
            NEW.totalcleared, 
            NEW.semidispatchcap
            into my_record;
            perform pg_notify('proddl',row_to_json(my_record)::text);
            return NEW;
        end;
    $function$
    ;
===== trigger

drop trigger if exists notify_predl

on pdl;

create trigger notify_pdl

after insert on pdl

for each row

when (new.duid = 'BLAH')

execute procedure notify_pdl();

This really has me tossed at the moment.

I forgot to ask a question. How do you debug this. For more information I have now restarted the entire client setup, have restarted the database, reinstalled the trigger function and the trigger and still this particular one is not running.

select * from pg_notification_query_usage() 

returns 0.

listening for the event in psql shows that it is not infact firing at all (I listened to one that is working as well, and did get that notification).

Data is definitely hitting this table with the duid set correctly. How do I work out why this is not firing now?

I changed the trigger to after insert or update and still nothing fires.

Nope, my mistake. I didn't see the notifications in my session for some reason, but they did make it into the queue, so it seems to be working now. No idea why it needs the 'on update' now when its been working fine for months, but it seems to be working.

[nishika]

The addition of the on update stanza seems to have fixed this issue. I still have no clue why it stopped triggering on just insert but there you have it.