Goal This query identifies row-level BEFORE triggers that validate data by querying other tables (SELECT ... FROM ... WHERE) before raising an exception, but fail to acquire a shared lock (FOR SHARE) on the referenced rows. Under PostgreSQL's default READ COMMITTED isolation level, reading a reference or classifier row (such as checking whether a status, category, or type is active) without FOR SHARE allows concurrent transactions to deactivate or modify that reference row before the triggering transaction commits, leading to write skew and data integrity violations.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
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,
    EXISTS (SELECT 1 FROM pg_constraint AS c WHERE c.oid=t.tgconstraint AND c.contype='t') AS is_constraint_trigger,
    t.tginitdeferred AS is_init_deferred
   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 Tr.trigger_schema, trigger_table, action_timing, event_manipulation, action_orientation, action_condition, string_agg(event_object_column::text, ',<br>' ORDER BY event_object_column) AS update_columns, is_constraint_trigger, is_init_deferred, Tr.trigger_name, routine_name, regexp_replace(routine_definition,'[\r\n]','<br>','g') AS routine_definition
FROM Tr 
LEFT JOIN information_schema.triggered_update_columns AS TUC 
  ON Tr.trigger_schema=TUC.trigger_schema AND Tr.trigger_name=TUC.trigger_name AND Tr.trigger_table=TUC.event_object_table
WHERE action_timing = 'BEFORE'
  AND action_orientation = 'ROW'
  -- Triger teeb SELECT päringu tabelist (v.a jsonb_each jms funktsioonid) ja viskab vea,
  -- kuid ei kasuta samas lauses FOR SHARE ega FOR UPDATE lukustust:
  AND routine_definition ~* '\ySELECT\y[^;]+\yFROM\s+(?!jsonb_each)[[:alnum:]_."]+[^;]+\yWHERE\y(?![^;]+\yFOR\s+(SHARE|UPDATE)\y)'
  AND routine_definition ~* '\yRAISE\s+EXCEPTION\y'
GROUP BY Tr.trigger_schema, Tr.trigger_table, action_timing, event_manipulation, action_orientation, action_condition, is_constraint_trigger, is_init_deferred, Tr.trigger_name, routine_name, regexp_replace(routine_definition,'[\r\n]','<br>','g')
ORDER BY Tr.trigger_schema, Tr.trigger_table, event_manipulation, action_timing, action_orientation, Tr.trigger_name;

Fixing Suggestion

Acquire a FOR SHARE lock on the referenced row using a positive existence check (e.g., IF NOT EXISTS (SELECT 1 FROM ref_table WHERE id = NEW.id AND is_active FOR SHARE) THEN RAISE EXCEPTION ...) so that the valid reference row remains locked and cannot be deactivated by a concurrent transaction before commit.

Collections

This query belongs to the following collections:

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 .

NameDescription
Find problems automaticallyQueries, 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:

Concurrency control

Queries of this category provide information about concurrency control.

Triggers and rules

Queries of this category provide information about triggers and rules in a database.

NameDescription
Concurrency controlQueries of this category provide information about concurrency control.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.