Can a PostgreSQL trigger fail silently without raising an error?

Question

I have a PostgreSQL table (table1) with an AFTER INSERT trigger that is supposed to copy the inserted data into another table (table2). However, I have noticed that while table1 consistently receives the new records, table2 sometimes does not.

There are no visible errors in the logs, and the trigger function seems to execute without raising any exceptions.

My questions are:

• Is it possible for a trigger in PostgreSQL to fail silently without any errors?
• What could cause the trigger to not insert records into table2 while still allowing the INSERT into table1 to succeed?
• Are there any best practices to debug this kind of issue?

Any insights or debugging strategies would be greatly appreciated!

Code:

CREATE TRIGGER trigger_order_backup

  AFTER INSERT ON orders

  REFERENCING NEW TABLE AS new_orders

  FOR EACH STATEMENT

  EXECUTE PROCEDURE trigger_copy_data();

CREATE OR REPLACE FUNCTION trigger_copy_data()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  IF (TG_TABLE_NAME = 'orders') THEN
    CASE TG_OP
      WHEN 'INSERT' THEN
        INSERT INTO order_backup (order_id, customer_id, total_amount)
        SELECT n.order_id, n.customer_id, n.total_amount
        FROM new_orders n
        INNER JOIN customers c ON c.customer_id = n.customer_id;

      WHEN 'UPDATE' THEN
        UPDATE order_backup b
        SET total_amount = n.total_amount
        FROM new_orders n        
        WHERE b.order_id = n.order_id;

      WHEN 'DELETE' THEN
        DELETE FROM order_backup b
        USING old_orders o
        WHERE b.order_id = o.order_id;
    END CASE;
  END IF;

  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

Answer 1

Is it possible for a trigger in PostgreSQL to fail silently without any errors?

Only from the perspective of the application business logic. The trigger fired, executed, the query in it found zero matching rows - that's a perfectly reasonable scenario. You can tie your business rules to the trigger function logic by adding an ASSERT which would throw an ASSERT_FAILURE exception whenever the trigger sees the resulting set is empty.

---

What could cause the trigger to not insert records into table2 while still allowing the INSERT into table1 to succeed?

The question doesn't provide the DDL or any sample data to work with but here are some guesses: Row Level Security, updatable views, function security definer vs security invoker and simple customers-orders mismatch.

1. There might be no customer entry that corresponds to the newly inserted order.

2. The incoming order could be missing a customer_id in it (it's null). A foreign key constraint alone won't prevent this as it does not imply a not null. Quoting the doc:

   There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default).

   (...)

   MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

3. An earlier trigger in your chain could be nullifying the incoming customer_id.

4. RLS policies could cause the user to see all the right rows in one table but miss some of them in the other.

5. Same with security invoker/definer, changing the user context and affecting what's visible from within the trigger function.

6. Since the DDL is still missing, one could also wager a guess some of those objects might be updatable views, and those can have a where cutting things out, and are also subject to security_invoker and security_barrier configuration.

Any of these would cause your inner join to result in an empty set:

INSERT INTO order_backup (order_id, customer_id, total_amount)
SELECT n.order_id, n.customer_id, n.total_amount
FROM new_orders n
INNER JOIN customers c ON c.customer_id = n.customer_id;----here

It might be a good idea to show all other triggers in the chain: it's likely the missing customer is supposed to be handled by another, earlier trigger that extracts it from the incoming order to upsert it into customers, and the actual failure happens back there.
_{demo at db<>fiddle}

begin; insert into orders values('order4','customer4',4);
       select*from orders;

order_id	customer_id	total_amount
order1	customer1	1
order2	customer2	2
order3	customer3	3
order4	customer4	4

The fourth order didn't make it into the _backup table:

       select*from order_backup;

order_id	customer_id	total_amount
order1	customer1	1
order2	customer2	2
order3	customer3	3

But if I go back and add the fourth customer that this order is supposed to be inner joined with:

rollback;
begin; insert into customers values('customer4');
       insert into orders values('order4','customer4',4);
       select*from order_backup;

order_id	customer_id	total_amount
order1	customer1	1
order2	customer2	2
order3	customer3	3
order4	customer4	4

---

Are there any best practices to debug this kind of issue?

In this case it's about debugging the query inside the trigger - the trigger function and the trigger definition are both perfectly fine.

In general, it's good to

• RAISE debug/info/notice messages - this could allow you to collect and print out counts of matches in customers and orders.
• Add assertion checks with ASSERT (here's an example from @Frank Heikens).
• Instead of, or in addition to logging things with RAISE, you might also consider saving the runtime info, like trigger variables or relevant counts, to a debug table (note the null at the end, responsible for the mismatch):

CREATE OR REPLACE FUNCTION trigger_copy_data()
  RETURNS TRIGGER AS
$BODY$
BEGIN
     create table if not exists _debug(name text,val text,type_ text,description text);
     insert into _debug values 
   ('NEW',NEW::text,'record','new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.')
  ,('OLD',OLD::text,'record','old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.')
  ,('TG_NAME',TG_NAME::text,'name','name of the trigger which fired.')
  ,('TG_WHEN',TG_WHEN::text,'text','BEFORE, AFTER, or INSTEAD OF, depending on the trigger''s definition.')
  ,('TG_LEVEL',TG_LEVEL::text,'text','ROW or STATEMENT, depending on the trigger''s definition.')
  ,('TG_OP',TG_OP::text,'text','operation for which the trigger was fired: INSERT, UPDATE, DELETE, or TRUNCATE.')
  ,('TG_RELID',TG_RELID::text,'oid (references pg_class.oid)','object ID of the table that caused the trigger invocation.')
  ,('TG_RELNAME',TG_RELNAME::text,'name','table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.')
  ,('TG_TABLE_NAME',TG_TABLE_NAME::text, 'name', 'table that caused the trigger invocation.')
  ,('TG_TABLE_SCHEMA',TG_TABLE_SCHEMA::text,'name','schema of the table that caused the trigger invocation.')
  ,('TG_NARGS',TG_NARGS::text,'integer' ,'number of arguments given to the trigger function in the CREATE TRIGGER statement.')
  ,('TG_ARGV',TG_ARGV::text, 'text[]' ,'arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.')
  ;
  IF (TG_TABLE_NAME = 'orders') THEN
    CASE TG_OP
      WHEN 'INSERT' THEN
        insert into _debug
        select 'incoming_ids'
              ,string_agg(customer_id,';')
              ,'text'
              ,'incoming customer identifiers'
        from new_orders;

        insert into _debug
        select 'matched_ids'
              ,string_agg(customer_id,';')
              ,'text'
              ,'incoming customer identifiers that match customers table'
        from new_orders
        inner join customers using(customer_id);

name	val	type_	description
NEW	null	record	new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.
OLD	null	record	old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.
TG_NAME	trigger_order_backup	name	name of the trigger which fired.
TG_WHEN	AFTER	text	BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.
TG_LEVEL	STATEMENT	text	ROW or STATEMENT, depending on the trigger's definition.
TG_OP	INSERT	text	operation for which the trigger was fired: INSERT, UPDATE, DELETE, or TRUNCATE.
TG_RELID	8618798	oid (references pg_class.oid)	object ID of the table that caused the trigger invocation.
TG_RELNAME	orders	name	table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.
TG_TABLE_NAME	orders	name	table that caused the trigger invocation.
TG_TABLE_SCHEMA	public	name	schema of the table that caused the trigger invocation.
TG_NARGS	0	integer	number of arguments given to the trigger function in the CREATE TRIGGER statement.
TG_ARGV	null	text[]	arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.
incoming_ids	customer4	text	incoming customer identifiers
matched_ids	null	text	incoming customer identifiers that match customers table