Do not cause potentially infinite loops. Recursive rules would fire itself over and over again. Although the system is able to detect these after executing a data modification statement it is better to avoid creating these altogether.
Notes
Rules are specific to PostgreSQL and thus it is not possible to get information about these from the INFORMATION_SCHEMA views. The query does not detect all possible recursions because multiple rules on different tables can also form a cycle. Different schemas could have tables with the same name. Thus, the query has to check that the table identifier is not a multipart identifier that also contains schema name.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH rules AS (SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename,
CASE WHEN ev_type='1' THEN 'SELECT' WHEN ev_type='2' THEN 'UPDATE' WHEN ev_type='3' THEN 'INSERT' ELSE 'DELETE' END AS event,
pg_get_ruledef(r.oid) AS definition
FROM pg_rewrite r
JOIN pg_class c ON c.oid = r.ev_class
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_authid u ON n.nspowner = u.oid
WHERE r.rulename <> '_RETURN' AND
(n.nspname = 'public' OR u.rolname <> 'postgres'))
SELECT schemaname, tablename, rulename, event, definition
FROM rules
WHERE definition ILIKE '% DO %' || event || '%' || tablename || '%'
AND definition NOT ILIKE '% DO %' || event || '%' || '.' || tablename || '%'
ORDER BY schemaname, tablename, rulename, event;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
WITH rules AS (SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename,
CASE WHEN ev_type='1' THEN 'SELECT' WHEN ev_type='2' THEN 'UPDATE' WHEN ev_type='3' THEN 'INSERT' ELSE 'DELETE' END AS event,
pg_get_ruledef(r.oid) AS definition
FROM pg_rewrite r
JOIN pg_class c ON c.oid = r.ev_class
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_authid u ON n.nspowner = u.oid
WHERE r.rulename <> '_RETURN' AND
(n.nspname = 'public' OR u.rolname <> 'postgres'))
SELECT format('DROP RULE %1$I ON %2$I.%3$I', rulename, schemaname, tablename) AS statements
FROM rules
WHERE definition ILIKE '% DO %' || event || '%' || tablename || '%'
AND definition NOT ILIKE '% DO %' || event || '%' || '.' || tablename || '%'
ORDER BY schemaname, tablename, rulename, event;
Drop the rule.
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.