This query identifies recursive triggers, which occur when a trigger's execution performs an operation (such as an UPDATE on the same table) that immediately causes the same trigger to fire again. This creates a potential infinite loop of execution. While PostgreSQL implements a stack depth limit to detect and terminate such runaway processes to prevent a complete system crash, relying on this fail-safe is poor engineering practice. These triggers consume significant system resources before failure and invariably result in aborted transactions. They should be refactored to avoid self-invocation.
Notes
The Tr subquery has been created based on the subquery of INFORMATION_SCHEMA.triggers view. In the returned body of routine the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser. The query does not detect all possible recursions because multiple triggers on different tables can also form a cycle.
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 (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, trigger_name, routine_name, regexp_replace(routine_definition,'[\r\n]',' ','g') AS routine_definition
FROM Tr
WHERE ((event_manipulation='INSERT' AND routine_definition~* ('[[:space:]]INSERT[[:space:]]+INTO[[:space:]]+' || trigger_table || '([[:space:]]|\()')) OR
(event_manipulation='UPDATE' AND routine_definition~*( '[[:space:]]UPDATE[[:space:]]+' || trigger_table || '[[:space:]]')) OR
(event_manipulation='DELETE' AND routine_definition~*( '[[:space:]]DELETE[[:space:]]+FROM[[:space:]]+' || trigger_table || '[[:space:]]')))
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.