Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

はじめに

サーバーリプレース作業にて、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」になりました。サブクエリのリターンが複数となっているのがエラー原因です。ということで一意になるようにプログラムを修正しました。

ファイル情報から取得

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

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)) 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フォルダにある該当ファイルのプロパティを見るとそうなっています。
postgre16394.png

改良版

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

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

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

SELECT
    table_name
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/PG_VERSION')) AS creation
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode)) 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')) AS createdate
   ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode)) 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')) AS createdate
        ,(SELECT modification FROM pg_stat_file('./base/' || folder || '/' || filenode)) 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 コマンドを使ってテーブルの再編成されてしまうと最終更新時刻も変更されてしまうので、あくまで参考情報です。
正確なファイル作成時刻と最終更新時刻を求めるなら専用テーブルと仕組みを作成するしかないですね。

yaju
静岡県島田市在住のシニアSEがコンピューター、機械学習、Unity、数学について考える
http://yaju3d.hatenablog.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした