CODEWITHSUNDEEP
mysqlrubypostgresqlauto-increment
John Hinnegan
John Hinnegan

Reputation: 5962

Get max id of all sequences in PostgreSQL

We have a monitor on our databases to check for ids approaching max-int or max-bigint. We just moved from MySQL, and I'm struggling to get a similar check working on PostgreSQL. I'm hoping someone can help.

Here's the query in MySQL

SELECT table_name, auto_increment FROM information_schema.tables WHERE table_schema = DATABASE();

I'm trying to get the same results from PostgreSQL. We found a way to do this with a bunch of calls to the database, checking each table individually.

I'd like to make just 1 call to the database. Here's what I have so far:

CREATE OR REPLACE FUNCTION getAllSeqId() RETURNS SETOF record AS
$body$
DECLARE
  sequence_name varchar(255);
BEGIN
  FOR sequence_name in SELECT relname FROM pg_class WHERE (relkind = 'S')
  LOOP
      RETURN QUERY EXECUTE 'SELECT last_value FROM ' || sequence_name;
  END LOOP;
  RETURN;
END
$body$
LANGUAGE 'plpgsql';
SELECT last_value from getAllSeqId() as(last_value bigint);

However, I need to somehow add the sequence_name to each record so that I get output in records of [table_name, last_value] or [sequence_name, last_value].

So I'd like to call my function something like this:

 SELECT sequence_name, last_value from getAllSeqId() as(sequence_name varchar(255), last_value bigint);

How can I do this?

EDIT: In ruby, this creates the output we're looking for. As you can see, we're doing 1 call to get all the indexes, then 1 call per index to get the last value. Gotta be a better way.

def perform
  find_auto_inc_tables.each do |auto_inc_table|
    check_limit(auto_inc_table, find_curr_auto_inc_id(auto_inc_table))
  end 
end 

def find_curr_auto_inc_id(table_name)
  ActiveRecord::Base.connection.execute("SELECT last_value FROM #{table_name}").first["last_value"].to_i
end 

def find_auto_inc_tables
  ActiveRecord::Base.connection.execute(
    "SELECT c.relname " +
    "FROM pg_class c " +                                                       
    "WHERE c.relkind = 'S'").map { |i| i["relname"] }
end

Upvotes: 1

Views: 5914

Answers (3)

cluesque
cluesque

Reputation: 1160

I stumbled on this question trying to answer the question I think OP is asking: "what tables are in danger of primary key rollover?"

This query examines columns with a default provided by a sequence (the way folks commonly configure "id" primary key columns) and compares the current value of the sequence to the capacity of the primary key, reporting the top ten and how close they are:

WITH column_info AS (
  SELECT t.relname AS table_name, c.attname AS column_name, c.atttypid, c.attlen AS length, y.typname AS type
         , pg_get_expr(adbin, adrelid) AS default
         , (regexp_matches(pg_get_expr(adbin, adrelid), 'nextval..(.*).::regclass'))[1] AS sequencename
  FROM pg_class t
  LEFT OUTER JOIN pg_attribute c ON c.attrelid = t.oid
  LEFT OUTER JOIN pg_type y ON y.oid = c.atttypid
  LEFT OUTER JOIN pg_attrdef d ON t.oid = d.adrelid AND d.adnum = c.attnum
  WHERE pg_get_expr(adbin, adrelid) LIKE '%nextval%'
),
max_values(length, max_value) AS (VALUES (4, 2147483647), (8, 9223372036854775807)),
sequence_info AS (
  SELECT table_name, column_name
       , (s.last_value * 100 / m.max_value) percent_of_limit
  FROM column_info ci
  JOIN pg_sequences s ON s.sequencename = ci.sequencename
  JOIN max_values m ON m.length = ci.length
  WHERE s.last_value IS NOT NULL
)
SELECT (table_name || '.' || column_name) AS name,
      percent_of_limit || '%' AS percent
FROM sequence_info
ORDER BY percent_of_limit DESC
LIMIT 10;

Upvotes: 1

Daniel Vérité
Daniel Vérité

Reputation: 61526

Your function seems quite close already. You'd want to modify it a bit to:

  • include the sequences names as literals
  • returns a TABLE(...) with typed columns instead of SET OF RECORD because it's easier for the caller

Here's a revised version:

CREATE OR REPLACE FUNCTION getAllSeqId() RETURNS TABLE(seqname text,val bigint) AS
$body$
DECLARE
  sequence_name varchar(255);
BEGIN
  FOR sequence_name in SELECT relname FROM pg_class WHERE (relkind = 'S')
  LOOP
      RETURN QUERY EXECUTE  'SELECT ' || quote_literal(sequence_name) || '::text,last_value FROM ' || quote_ident(sequence_name);
  END LOOP;
  RETURN;
END
$body$
LANGUAGE 'plpgsql';

Note that currval() is not an option since it errors out when the sequence has not been set in the same session (by calling nextval(), not sure if there's any other way).

Upvotes: 8

Andrew Lazarus
Andrew Lazarus

Reputation: 19320

Would something as simple as this work?

SELECT currval(sequence_name) from information_schema.sequences;

If you have sequences that aren't keys, I guess you could use PG's sequence name generation pattern to try to restrict it.

SELECT currval(sequence_name) from information_schema.sequences
WHERE sequence_name LIKE '%_seq';

If that is still too many false positives, you can get table names from the information_schema (or the pg_* schemata that I don't know very well) and refine the LIKE parameter.

Upvotes: 0

Related Questions