Identify the number of sequence generators in different schemas. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys at the database level. An alternative is to implement generation of unique values at the application level or at the database level by using triggers. However, such implementation would most probably lead to the performance penalty because adding new rows to the table must be serialized, i.e., it can be done in one session at a time.
Notes
The query considers both external sequence generators (are produced by executing CREATE SEQUENCE statement or by using SMALLSERIAL/SERIAL/BIGSERIAL notation) as well as internal sequence generators (are created for identity columns). The query only returns data about schemas that have at least one sequence generator. The query uses information_schema.schemata view in order to find initially all the schemas - the reason is that a schema might have only external sequence generators, only internal sequence generators, or both.
Type
Sofware measure (Numeric values (software measures) about the database)
WITH seq_stats AS (SELECT schema_name, Count(DISTINCT s.sequence_name) AS number_of_external_sequences, (SELECT Count(*) AS cnt FROM
information_schema.columns AS c WHERE c.table_schema=sh.schema_name AND c.is_identity='YES') AS number_of_internal_sequences
FROM information_schema.schemata AS sh LEFT JOIN information_schema.sequences AS s
ON sh.schema_name=s.sequence_schema
WHERE (schema_name='public' OR
(schema_owner<>'postgres' AND schema_name IS NOT NULL))
GROUP BY schema_name)
SELECT schema_name, number_of_external_sequences, number_of_internal_sequences, number_of_external_sequences+number_of_internal_sequences AS total_number_of_sequences
FROM seq_stats
WHERE number_of_external_sequences+number_of_internal_sequences>0
ORDER BY schema_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Find quick numeric overview of the database
Queries that return numeric values showing mostly the number of different types of database objects in the database
Categories
This query is classified under the following categories:
Name
Description
Comfortability of data management
Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
Sequence generators
Queries of this category provide information about sequence generators and their usage.