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
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.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Alter the sequence so that it will be owned by a column.
Data Source
system catalog only
SQL Query
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;
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
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Sequence generators
Queries of this category provide information about sequence generators and their usage.