获取PostgreSQL中所有序列的最大ID [英] Get max id of all sequences in PostgreSQL

问题描述

我们在数据库上有一个监视器，以检查ID是否接近max-int或max-bigint。我们刚从MySQL迁出，而我正努力在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.

这是MySQL中的查询

Here's the query in MySQL


SELECT table_name，在information_schema.tables中为auto_increment，其中table_schema = DATABASE（）;


我正在尝试从PostgreSQL获得相同的结果。我们找到了一种方法，可以对数据库进行一堆调用，分别检查每个表。

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

但是，我需要以某种方式将sequence_name添加到每个记录中，以便在[ table_name，last_value]或[sequence_name，last_value]。

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

我该怎么做？

编辑：在红宝石中，这将创建我们想要的输出。如您所见，我们正在执行1次调用以获取所有索引，然后对每个索引进行1次调用以获取最后一个值。

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 

推荐答案

您的功能似乎已经很接近了。您需要对其进行一些修改，以使其：

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

• 将序列名称包括为文字


• 返回带有键入列的 TABLE（...）而不是 SET OF RECORD 的类型，因为调用者更容易

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

这是修订版：

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（），不确定是否还有其他方法）。

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

这篇关于获取PostgreSQL中所有序列的最大ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！