Find triggers on base tables that are used to calculate tsvector values that react to a wrong set of events, i.e., react to the DELETE event or do not react to the INSERT and UPDATE events.
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,
p.proname
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)
AND c.relkind IN ('r','p')
AND p.proname~*'^tsvector_update_trigger')
SELECT Tr.trigger_schema, Tr.trigger_table,
string_agg(Tr.event_manipulation,',' ORDER BY Tr.event_manipulation) AS events
FROM Tr
GROUP BY Tr.trigger_schema, Tr.trigger_table
HAVING string_agg(Tr.event_manipulation,',' ORDER BY Tr.event_manipulation) !~*'INSERT.*UPDATE'
OR string_agg(Tr.event_manipulation,',' ORDER BY Tr.event_manipulation) ~*'DELETE'
ORDER BY Tr.trigger_schema, Tr.trigger_table;
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
Full text search
Queries of this category provide information about full text search
Triggers and rules
Queries of this category provide information about triggers and rules in a database.