Broken database trigger in postgresql
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$ ;
drop trigger if exists notify_predl
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()
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.
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.