Postgresql: Trigger is not working at times

Question

I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled".

My trigger code is as follows:

CREATE OR REPLACE FUNCTION audit_src_exhibit() RETURNS trigger AS $BODY$
BEGIN
 IF TG_OP = 'INSERT' then
 if new.audit_created_date is null THEN
 new.audit_created_date := current_timestamp;
 new.audit_created_by := session_user::text;
 end if;
 else
 if new.audit_modified_date is null THEN
 new.audit_modified_date := current_timestamp;
 new.audit_modified_by := session_user::text;
 end if;
 END IF;
 RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER audit_src_exhibit_tr
BEFORE INSERT OR UPDATE ON <table>
FOR EACH ROW EXECUTE PROCEDURE audit_src_exhibit();

Is there any specific reason for this behaviour? Does my code show any signs of known issues which would result in triggers not firing?

I find the audit columns populated as null when some insert happened today

select audit_created_date 
from sd_source_exhibit_link
where audit_created_date is null;

result of query

MY Table DDL :

CREATE TABLE source_domain.sd_source_exhibit_link (
 source_exhibit_link_id serial DEFAULT nextval('sd_source_exhibit_link_source_exhibit_link_id_seq'::regclass) NOT NULL,
 source_exhibit_id varchar(300),
 source_entity_id varchar(300),
 created_by_provenance varchar(200),
 occurence_id varchar(200),
 source_matched_percentage int4,
 link_type varchar(200),
 soft_deleted varchar(50),
 is_inner_exhibit bool DEFAULT false,
 is_skinny_exhibit bool DEFAULT false NOT NULL,
 provided_by varchar(500),
 audit_created_date timestamptz NOT NULL,
 audit_created_by varchar(100) NOT NULL,
 audit_modified_date timestamptz,
 audit_modified_by varchar(100),
 CONSTRAINT sd_source_exhibit_link_pkey PRIMARY KEY (source_exhibit_link_id)
);
CREATE INDEX idx_source_exhibit_link ON source_domain.sd_source_exhibit_link (occurence_id);

• Adrian Klaver: Go here SQL Formatter and reformat the function to make it more readable and easier to follow the logic.
• Laurenz Albe: This question needs more work from your side. First, the code is syntactically wrong (END IF before ELSE). Please post your actual code or (bedder) a minimal reproducer that you tested. Then you claim that the trigger is not firing sometimes. How do you determine that? What is the exact DML statement that should invoke the trigger but doesn't? What is the resulting row, and how does that differ from what you expect?
• Luuk: @LaurenzAlbe: Please take a close look, the code has nested if statements, and a lousy formatting......
• Laurenz Albe: @Luuk Ugh, you are right. The rest of my comment is still useful though.
• amacvar: @LaurenzAlbe, i believe he determines that from the pic with the null column output. And is unable to determine the source. i've suggested he change the "created" columns to be not null, hopefully he can provide more clarity and answer your other questions.

Answer 1

I think the issue isn't that the trigger isn't firing.

Looking at the trigger code, you only check if new.audit_created_date is null if TG_OP = 'INSERT'.

That means you can perform an UPDATE, setting audit_create_date to NULL; the trigger would still fire, but the first branch of the IF, which sets new.audit_create_date to current_timestamp, would not execute as TG_OP is not equal to INSERT.

In that case, new.audit_create_date would still be NULL after trigger execution.

Answer 2

there are a lot of IF statement without else statement that can generate the situation you are telling.

Examples:

1. TG_OP is always INSERT? Or can be "Insert"? (case sensitive problem)
2. IF "new.audit_created_date is null".. else???
3. and again similar.

For debug, you can think about a table in which you write when TRIGGER do nothing.

ExAMPLES:

1. IF TG_OP = 'INSERT' then "doWork()" else "add in table "tg_op = value"".

I dont' think database not fire insert, I think you are skipping some IF branch.

• Adrian Klaver: Read the docs plpgsql trigger TG_OP Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired. This is not really an answer.

Answer 3

As @cogitoergosum mentioned above that's a DML possibility that nulls the created columns.
Or if session_replication_role is set to 'replica'.

Also to catch the errant DML, look into the possibility of changing the audit_created_* columns' constraint to not null. Since based on the trigger code, it seems like the intention is for those columns to always be populated.

it could also help if the table DDL is shared.

• Arun: my session_replication_role -->'Origin'
• Arun: Tested this addin not not null on audit_created_date & audit_created_by since this needed on every insert and it is working but my issue intermittent dont know when this trigger actaully wont fire
• Arun: Added the ddl in my question itself
• amacvar: well, after adding the not null constraint, now all you have to do is wait for any applications/ users which error out. Check the logs. and hopefully your log_line_prefix will help ascertain that. Also FYI, any session setting is valid only for that session, if you wanted to test this in your session (the role setting i mentioned is a remote possibility) I would also suggest replying to Laurenz and improve the formatting for future reference