postgresql - 如何在函数中更新 EXECUTE 格式 block 的结果 (PostgreSQL)

Question

下面是一个很棒的函数，可以检查 PostgreSQL 数据库中所有表的实际计数。我找到了here .

从我的本地测试来看，该函数似乎是在对100张表全部统计完后才返回全部结果。

我正在努力让它更实用。如果我们能够在每张表统计完后立即保存结果，那么我们就可以查看所有统计工作的进度，而不用等到结束。

我想如果我能在完成第一个表后立即更新此函数中的结果，那将非常适合我的要求。

这个函数完成第一个表的计数后，请问如何将结果更新到表中？

CREATE FUNCTION rowcount_all(schema_name text default 'public')
  RETURNS table(table_name text, cnt bigint) as
$$
declare
 table_name text;
begin
  for table_name in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
    ORDER BY c.relname
  LOOP
    RETURN QUERY EXECUTE format('select count(*) from %I.%I',
       table_name, schema_name, table_name);
  END LOOP;
end
$$ language plpgsql;

-- Query

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

已更新

我决定使用 shell 脚本将下面的函数作为后台进程运行。该函数将生成一个处理日志文件，以便我可以检查当前进程。

Answer 1

我认为您的想法很好，但我也不认为它可以在 PostgreSQL 上“开箱即用”。我绝不是这方面的专家，但是 MVCC 在 PostgreSQL 上的工作方式，它基本上是在最好理解为临时空间的地方执行所有 DML，然后如果一切都按预期工作，它将全部移入在最后。

这有很多优点，最值得注意的是，当有人更新表时，它不会阻止其他人从相同的表中查询。

如果这是 Oracle，我认为您可以使用 commit, 在存储过程中完成此操作，但这不是 Oracle。公平地说，Oracle 不允许像 PostgreSQL 那样在存储过程中回滚截断，因此需要付出代价。

再说一次，我不是专家，所以如果我搞砸了一两个细节，请随时纠正我。

所以，回到解决方案。您可以完成此操作的一种方法是将您的服务器设置为远程服务器。这样的事情会起作用:

CREATE SERVER pgprod
FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (dbname 'postgres', host 'localhost', port '5432');

假设您有一个存储表和计数的表:

create table table_counts (
   table_name text not null,
   record_count bigint,
   constraint table_counts_pk primary key (table_name)
);

如果不是因为您希望看到这些结果，那么对于单个模式，这样的事情会起作用。制作所有模式很容易，所以这是为了说明:

CREATE or replace FUNCTION rowcount_all(schema_name text)
  returns void as
$$
declare
 rowcount integer;
 tablename text;
begin
  for tablename in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
    ORDER BY c.relname
  LOOP
    EXECUTE 'select count(*) from ' || schema_name || '.' || tablename into rowcount;
    insert into table_counts values (schema_name || '.' || tablename, rowcount)
    on conflict (table_name) do 
    update set record_count = rowcount;
  END LOOP;
end
$$ language plpgsql;

(这假定 9.5 或更高——如果不是，请手动滚动您自己的更新)。

但是，由于您希望对表进行实时更新，因此可以将相同的更新插入到 dblink 表达式中:

    perform dblink_exec('pgprod', '
         << your upsert statement here >>
    ');

当然，DBlink 中 SQL 的格式设置现在有点棘手，但好处是一旦您确定了它，您就可以在后台运行该函数并在它运行时查询表以查看动态结果。

我会权衡一下是否需要真正实时获取信息。