在 PGSQL 13 用 Stored Procedure 包裝 VACUUM 與 ANALYZE 指令

 使用 ETL 作業作資料匯入，常常會有大量資料匯入或異動。對 PGSQL 來說，這時候若可以在批次資料匯入/異動完畢後執行 ANALYZE 作一次資料表的抽樣統計收集，甚至是 VACUUM 維護作業，會有助於資料的查詢。

然而。在 PGSQL 裡面，可以執行 VACUUM 與 ANALYZE 指令的帳號，只有表格物件擁有者（ Table Owner ）以及 postgres/enterprisedb 等 Superuser 帳號，但是通常 ETL 程式用的帳號通常不會是這兩個。。。因此若有 vacuum analyze 包裝函數，搭配 Stored Function/Procedure 的 Security Definer 供其他帳戶呼叫，就會方便許多。

這篇筆記紀錄的就是 PGSQL 批次匯入後執行 ANALYZE 的 Stored Procedure，也順便練習 Stored Procedure 一下～

一開始設想這樣的功能很容易～殊不知。。。VACUUM 不能放到 transaction 裡面。

不過好在 ANALYZE 作業是可以包裝進去 procedure 的～先處理 ANALYZE。

以下主要是參照參考資料微調的 procedure，用來對整個 Schema 執行 ANALYZE。記得匯入本 Script 要使用 Superuser 或是 Schema&Table Owner：

CREATE OR REPLACE PROCEDURE analyze_schema(schema_Name VARCHAR DEFAULT 'public') 
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  tab RECORD;
BEGIN
  for tab in (select tablename 
                FROM pg_tables t
                WHERE schemaname = schema_Name
                order by 1)
  LOOP
    RAISE NOTICE 'ANALYZE %.%', schema_Name, tab.tablename;
    EXECUTE 'ANALYZE '||quote_ident(schema_Name)||'.'||quote_ident(tab.tablename);
  end loop;
end
$$;

只要具備這個函數的執行權限，就可以叫 Superuser 代為操作 ANALYZE 了。

另外。裡面用了 Dynamic SQL 的 SQL 字串執行 SQL，因此要記得用 quote_ident() 避免 SQL Injection。

接著處理 VACUUM：由於 VACUUM 屬於 non-transactional 功能（回收空間的動作涉及 OS 層的作業），因此無法支援 BEGIN/ROLLBACK 或 BEGIN/COMMIT，也就無法直接放置到 Stored Function/Procedure 了。

但是～可以利用 dblink 處理，因為 DBLink 是另外開啟一個 Session 連線到資料庫，就不會被現在卡到。

CREATE OR REPLACE PROCEDURE vacuum_schema(schema_name text)
language plpgsql 
SECURITY DEFINER
as
$$
declare
    rec record;
begin
    perform dblink_connect('dbname='|| current_database());
    for rec in
        select tablename from pg_tables 
        where schemaname = schema_name
    loop
        RAISE NOTICE 'VACUUM %.%', schema_Name, rec.tablename;
        perform dblink('VACUUM ' || quote_ident(schema_name) || '.' 
                                 || quote_ident(rec.tablename));
    end loop;
    perform dblink_disconnect();
end;
$$;

以上函數寫的比較簡易，仰賴 pg_hba.conf 的 local ident 規則，免於放帳號密碼～～

這些函數的呼叫也很單純

select analyze_schema('some_schema');

select vacuum_schema('some_schema');

其實以上的也可以包裝成 function（回傳值 VOID）。也就是說也可以在 PGSQL 12 之前使用（PGSQL 12 版之前尚未支援 Procedure 功能）。

最後，使用這些函數，也記得要考量匯入作業頻率與執行 VACUUM/ANALYZE 的拿捏～若非常頻繁的匯入頻率，或許反而適合另外排程定時 VACUUM ANALYZE 會比較好～



參考資料
postgresql - Postgres Vacuum in Function - Database Administrators Stack Exchange
postgresql - VACUUM cannot be executed from a function or multi-command string - Stack Overflow
Vacuum a PostgreSQL schema's tables via dblink -  adunstan/vacuum_schema.sql
Executing VACUUM by non-owner user – Luca Ferrari – Open Source advocate, human being
postgresql - Vacuum analyze all tables in a schema postgres - Stack Overflow