PostgreSQLテーブルの作成日時と更新日時の取得について

はじめに

サーバーリプレース作業にて、Windows Server 2008R2 + Oracle 11g から Windows Server 2016 + PostgreSQL 9.6 に移行作業をしています。自社アプリケーションの中にSQLServer側にレプリケーションするためにトリガーを作成するツールがありまして、テーブル情報として作成日時と更新日時が表示されていました。

Oracleではテーブル情報(USER_OBJECTS)を利用して、作成日時(created)と更新日時(last_ddl_time)が取得できます。

PostageSQLではテーブル情報(pg_stat)を参照しても、作成日時や更新日時に該当する項目はないようです。調べる限りでは、PostageSQLではそのような情報は保持する仕組みにはなっていないとのこと。

アプリケーション上は作成日時と更新日時は参考情報程度のものなので無くても問題にはならないが、参考情報程度であれば多少違っていても悪くない。

【2019/08/03追加】
スキーマ違いで同じテーブル名があると「ERROR: more than one row returned by a subquery used as an expression」になりました。サブクエリのリターンが複数となっているのがエラー原因です。ということで一意になるようにプログラムを修正しました。

【2022/11/23追加】
フォルダやファイルが存在しなかった場合に例外エラー(File Not Found)が出ていたが、pg_stat_fileの第二引数にmissing_okがあることを知りました。
missing_okパラメータは、ファイルまたはディレクトリが存在しない場合の動作を指定できます。 trueの場合、関数はNULLを返します。 falseの場合はエラーが発生します。 デフォルトはfalseです。

「pg_stat_file」のみで検索した時にトップに出てくるのが、PosgtgreSQL 8.1のドキュメントなんですよね。このバージョンだと、まだmissing_okパラメータがないんですよね。URLの8.1のところを9.6に変更したドキュメントには、missing_okパラメータの説明が記載されています。なので、気が付くのが遅れた。
https://www.postgresql.jp/document/9.6/html/functions-admin.html

ファイル情報から取得

調べてみると、$PGDATA/base/{database id}/{table filenode id} で取得できそうなことが分かった。

• PostgreSQL：テーブル作成時間
• PostgreSQL: Table creation time
• Automatically Drop Tables and Indexes Older than 90 Days [duplicate]

pg_stat_file関数ではファイル作成時刻(creation:Windowsのみ)、最終更新時刻(modification)が取得できるので、これを利用する。

ちなみに、GreenplumというPostgreSQLを元に開発された並列処理可能なRDBには、pg_stat_last_operationテーブルがあり、CREATEした日時が取得できるようです。
超高速のPostgreSQLとしてGreenplumを気軽に使う

ソースコード

ファイル作成時刻と最終更新時刻の2つをサブクエリで取得するとファイルを2回アクセスするので、一度に済ませる方法を探したら、配列コンストラクタという方法を見つけた。
サブクエリで複数カラムを返す

※table_schema = 'public' は任意のスキーマに変更してください。

SELECT
    table_name
   ,arr[1] AS creation
   ,arr[2] AS modification
FROM
(
    SELECT
         table_name
        ,(SELECT ARRAY[creation,modification] 
          FROM pg_stat_file('./base/' || folder || '/' || filenode, true)) AS arr
    FROM (
        SELECT
            table_name
           ,(SELECT MAX(pg_ls_dir::int)::text
             FROM pg_ls_dir('./base')
             WHERE pg_ls_dir <> 'pgsql_tmp'
               AND pg_ls_dir::int  <= 
            (SELECT relfilenode FROM pg_class cls
             INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
             WHERE nsp.nspname = table_schema AND relname ILIKE table_name)) AS folder 
           ,(SELECT relfilenode FROM pg_class cls
             INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
             WHERE nsp.nspname = table_schema AND relname ILIKE table_name) AS filenode
        FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
      AND table_schema = 'public'
   ) CTE
) X

結果

table_name	creation	modification
Fuga	2018-09-15 14:01:50+09	2018-09-15 14:01:50+09
Hoge	2018-09-15 14:03:01+09	2018-09-15 14:03:01+09

Hoge テーブルにカラムを追加してみた。

table_name	creation	modification
Fuga	2018-09-15 14:01:50+09	2018-09-15 14:01:50+09
Hoge	2018-09-15 15:49:01+09	2018-09-15 15:49:01+09

ファイル作成時刻と最終更新時刻が同じになってしまいました。確かにBaseフォルダにある該当ファイルのプロパティを見るとそうなっています。

改良版

この回避策は、 PG_VERSIONが作成後に変更される可能性が最も低いと想定しています。
PostgreSQL：テーブル作成時間

ファイル作成時刻(creation)はWindows版のみのようですし、最終更新時刻(modification)のみを対象にする。ファイル作成時刻は変更される可能性が最も低いPG_VERSIONの最終更新時刻を使う。

※table_schema = 'public' は任意のスキーマに変更してください。

SELECT
    table_name
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/PG_VERSION', true)) AS creation
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode, true)) AS modification
FROM (
    SELECT
        table_name
       ,(SELECT MAX(pg_ls_dir::int)::text
         FROM pg_ls_dir('./base')
         WHERE pg_ls_dir <> 'pgsql_tmp'
           AND pg_ls_dir::int  <= 
        (SELECT relfilenode FROM pg_class cls
         INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
         WHERE nsp.nspname = table_schema AND relname ILIKE table_name)) AS folder 
       ,(SELECT relfilenode FROM pg_class cls
         INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
         WHERE nsp.nspname = table_schema AND relname ILIKE table_name) AS filenode
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
      AND table_schema = 'public'
) CTE

table_name	creation	modification
Fuga	2018-09-15 13:56:29+09	2018-09-15 14:01:50+09
Hoge	2018-09-15 13:56:29+09	2018-09-15 15:49:01+09

※ファイル作成時刻(creation)は変更される可能性が最も低いPG_VERSIONの最終更新時刻です。

VIEW作成

こういうのは、ビューを作成するといいでしょう。

※table_schema = 'public' は任意のスキーマに変更してください。

CREATE OR REPLACE VIEW view_table_stat AS
SELECT
    table_name::text AS tablename
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/PG_VERSION', true)) AS createdate
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode, true)) AS updatedate
FROM (
   SELECT
       table_name
      ,(SELECT MAX(pg_ls_dir::int)::text
        FROM pg_ls_dir('./base')
        WHERE pg_ls_dir <> 'pgsql_tmp'
          AND pg_ls_dir::int  <= 
       (SELECT relfilenode FROM pg_class cls
        INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
        WHERE nsp.nspname = table_schema AND relname ILIKE table_name)) AS folder 
      ,(SELECT relfilenode FROM pg_class cls
        INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
        WHERE nsp.nspname = table_schema AND relname ILIKE table_name) AS filenode
   FROM information_schema.tables
   WHERE table_type = 'BASE TABLE'
     AND table_schema = 'public'
) CTE;

関数作成

ついでに関数を生成してみました。
レコードを返す関数を作成するのに思ってたより情報がなくて苦労しました。
ビューで済むので関数作る意味はないんだけど、何かの参考にはなるでしょう。

※table_schema = 'public' は任意のスキーマに変更してください。

CREATE OR REPLACE FUNCTION get_table_stat() 
RETURNS TABLE (
    tablename text
   ,createdate timestamp with time zone
   ,updatedate timestamp with time zone) AS $$
BEGIN
    RETURN QUERY
    SELECT
         table_name::text AS tablename
        ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/PG_VERSION', true)) AS createdate
        ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode, true)) AS updatedate
    FROM (
        SELECT
           table_name
          ,(SELECT MAX(pg_ls_dir::int)::text
            FROM pg_ls_dir('./base')
            WHERE pg_ls_dir <> 'pgsql_tmp'
              AND pg_ls_dir::int  <= 
           (SELECT relfilenode FROM pg_class cls
            INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
            WHERE nsp.nspname = table_schema AND relname ILIKE table_name)) AS folder 
          ,(SELECT relfilenode FROM pg_class cls
            INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
            WHERE nsp.nspname = table_schema AND relname ILIKE table_name) AS filenode
        FROM information_schema.tables
        WHERE table_type = 'BASE TABLE'
          AND table_schema = 'public'
    ) CTE;
END
$$ LANGUAGE plpgsql;

-- 使い方
SELECT * FROM get_table_stat()

最後に

CLUSTER コマンドや VACUUM FULL コマンドを使ってテーブルの再編成されてしまうと最終更新時刻も変更されてしまうので、あくまで参考情報です。
正確なファイル作成時刻と最終更新時刻を求めるなら専用テーブルと仕組みを作成するしかないですね。