| Query goal: | Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place. |
| Notes about the query: | The query checks as to whether a sequence depends on a column so that if the table/column is dropped, then the sequence will be dropped as well. The query also takes into account that if a column is an identity column, then internally an associated sequence generator is created that will be dropped if the table/column will be dropped. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | High (Few or no false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Alter the sequence so that it will be owned by a column. |
| Data source: | system catalog only |
| SQL query: | Click on query to copy it
WITH sequences AS (
SELECT
c.oid,
n.nspname AS schema_name,
c.relname AS sequence_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind='S'
)
SELECT schema_name, sequence_name
FROM sequences seq
WHERE NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d
WHERE d.objid=seq.oid
AND d.deptype IN ('a','i')
AND d.refobjsubid<>0)
ORDER BY schema_name, sequence_name; |