I'm trying to write a query that I can use in a monitor to warn me if I'm close to exceeding the 32bit integer space for a sequence of IDs.

I know I can get a list of all tables like so:

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'

And then loop over each one, querying (e.g.):

SELECT last_value FROM users_id_seq

1. Is there a way to combine those into a single query?
2. Is there a way to exclude tables that are already using bigint for their ID?

CodePudding user response：

To find the table a sequence is "related" to, you can use something like this:

SELECT seq_ns.nspname as sequence_schema,
       seq.relname    as sequence_name,
       tab_ns.nspname as table_schema,
       tab.relname    as related_table,
       s.last_value,
       s.max_value
FROM pg_catalog.pg_class seq
         INNER JOIN pg_catalog.pg_namespace seq_ns ON seq.relnamespace = seq_ns.oid
         INNER JOIN pg_catalog.pg_depend d ON d.objid = seq.oid AND d.deptype = 'a'
         INNER JOIN pg_catalog.pg_class tab ON d.objid = seq.oid AND d.refobjid = tab.oid
         INNER JOIN pg_catalog.pg_namespace tab_ns ON tab.relnamespace = tab_ns.oid
         INNER JOIN pg_catalog.pg_sequences s ON s.schemaname = seq_ns.nspname AND s.sequencename = seq.relname
WHERE seq.relkind = 'S'
  AND seq_ns.nspname = '<your-schema-name>';