PostgreSQL のすべてのシーケンスの最大 ID を取得する

概要

データベースには、max-int または max-bigint に近づいている ID をチェックするモニターがあります。私たちは MySQL から移行したばかりですが、PostgreSQL で同様のチェックを機能させるのに苦労しています。誰かが助けてくれることを願っています。

MySQL のクエリは次のとおりです。

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

PostgreSQLから同じ結果を得ようとしています。私たちは、データベースを何度も呼び出し、各テーブルを個別にチェックすることでこれを行う方法を見つけました。

データベースに対して 1 回だけ呼び出しを行いたいと考えています。これまでに私が持っているものは次のとおりです。

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);

ただし、[table_name, last_value] または [sequence_name, last_value] のレコードに出力を取得するには、何らかの方法で sequence_name を各レコードに追加する必要があります。

したがって、関数を次のように呼び出したいと思います。

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

これどうやってするの？

編集: Ruby では、これにより、探している出力が作成されます。ご覧のとおり、すべてのインデックスを取得するために 1 回の呼び出しを実行し、次に最後の値を取得するためにインデックスごとに 1 回の呼び出しを実行しています。もっと良い方法が必要です。

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

解決策

あなたの関数はすでにかなり近づいているようです。これを次のように少し変更するとよいでしょう。

改訂版は次のとおりです。

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';

currval() はオプションではないことに注意してください。同じセッション内でシーケンスが設定されていない場合 (nextval() を呼び出すことにより、他の方法があるかどうかはわかりませんが) エラーが発生するためです。