Query goal: | Find base tables that have multiple triggers to update tsvector values. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Low (Many false-positive results) |
Query license: | MIT License |
Data source: | system catalog only |
SQL query: | Click on query to copy it
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, Tr.proname, Count(DISTINCT Tr.trigger_name) AS number_of_triggers, string_agg(Tr.event_manipulation,',' ORDER BY Tr.event_manipulation) AS events FROM Tr GROUP BY Tr.trigger_schema, Tr.trigger_table, Tr.proname HAVING Count(DISTINCT Tr.trigger_name)>1 ORDER BY Tr.trigger_schema, Tr.trigger_table; |
Collection name | Collection 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 . |
Category name | Category description |
---|---|
Duplication of implementation elements | Queries of this catergory provide information about the duplication of the database objects. |
Full text search | Queries of this category provide information about full text search |
Reference |
---|
https://www.postgresql.org/docs/current/textsearch-features.html |