This query identifies row-level BEFORE triggers on base tables that execute a RETURN NULL statement without raising a corresponding exception. In PostgreSQL, returning NULL from a BEFORE trigger silently aborts the pending INSERT, UPDATE, or DELETE operation for the current row. Unlike an exception, which alerts the calling application to the failure, a silent cancellation allows the transaction to proceed as if successful, but with the data modification discarded. This behavior is often unintentional (e.g., a forgotten RETURN NEW) and poses a significant risk of data loss and difficult-to-debug application logic errors.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH Tr AS (SELECT
n.nspname AS trigger_schema,
t.tgname AS trigger_name,
em.text AS event_manipulation,
c.relname AS trigger_table,
np.nspname AS routine_schema,
p.proname AS routine_name,
p.prosrc AS routine_definition,
(SELECT DISTINCT action_condition
FROM INFORMATION_SCHEMA.triggers AS it
WHERE it.trigger_schema=n.nspname
AND it.event_object_table=c.relname
AND it.trigger_name=t.tgname) AS action_condition,
CASE t.tgtype::integer & 1
WHEN 1 THEN 'ROW'
ELSE 'STATEMENT'
END AS action_orientation,
CASE t.tgtype::integer & 66
WHEN 2 THEN 'BEFORE'
WHEN 64 THEN 'INSTEAD OF'
ELSE 'AFTER'
END AS action_timing
FROM pg_namespace n,
pg_class c,
pg_trigger t,
pg_proc p,
pg_namespace np,
( VALUES (4,'INSERT'), (8,'DELETE'), (16,'UPDATE'), (32,'TRUNCATE')) em(num, text)
WHERE n.oid = c.relnamespace
AND c.oid = t.tgrelid
AND t.tgfoid=p.oid
AND p.pronamespace=np.oid
AND c.relkind='r'
AND (t.tgtype::integer & em.num) <> 0
AND NOT t.tgisinternal
AND NOT pg_is_other_temp_schema(n.oid))
SELECT trigger_schema, trigger_table, action_timing, event_manipulation, action_orientation, action_condition, routine_name, regexp_replace(routine_definition,'[\r\n]',' ','g') AS routine_definition
FROM Tr
WHERE action_timing ='BEFORE'
AND action_orientation='ROW'
AND routine_definition~* 'RETURN[[:space:]]+NULL'
AND routine_definition!~* 'RETURN[[:space:]]+(OLD|NEW)'
AND routine_definition!~* 'RAISE[[:space:]]+EXCEPTION'
ORDER BY trigger_schema, trigger_table, routine_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
Triggers and rules
Queries of this category provide information about triggers and rules in a database.