pg_pageinspect_plus を作りました

作成したPostgreSQL拡張 [pg_pageinspect_plus](https://github.com/harukat/pg_pageinspect_plus) の紹介記事です。

# PostgreSQL の生データ解析

PostgreSQLのデータが物理レベルで破損したみたい、という場合に、どう壊れたのか調べたり、救出できるデータは無いかを探るには、[contribモジュール](https://www.postgresql.jp/document/current/html/contrib.html) として付属している [pageinspect](https://www.postgresql.jp/document/current/html/pageinspect.html) 拡張を使うのが便利です。

PostgreSQLのテーブルデータは （デフォルトで）8KB のページ（ブロックともいう）が連なる 1つのファイル（1GBを超えると分割される）に格納されています。
テーブルに対応したファイルの各ページについて、pageinspect を使って以下のように解析できます。

```sql
db=# CREATE EXTENSION pageinspect;
CREATE EXTENSION

db=# SELECT * FROM public.t1;
 id  |  txt  |         ts          | bool |      i8
-----+-------+---------------------+------+--------------
 100 | ABCDE | 2020-08-01 12:00:00 | t    |     98765432
 101 | abcde | 2025-08-01 12:00:00 | f    | 554433221100
(2 rows)

db=# SELECT tuple_data_split('public.t1'::regclass, t_data, t_infomask, t_infomask2, t_bits), *
      FROM heap_page_items(get_raw_page('public.t1', 0));
                                   tuple_data_split                                    | lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff |  t_bits  | t_oid |                                       t_data
---------------------------------------------------------------------------------------+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------+-------+------------------------------------------------------------------------------------
 {"\\x64000000","\\x0d4142434445","\\x00f0e069ce4e0200","\\x01","\\x780ae30500000000"} |  1 |   8128 |        1 |     64 |    501 |      0 |        0 | (0,1)  |           5 |      11010 |     24 |          |       | \x640000000d414243444500000000000000f0e069ce4e02000100000000000000780ae30500000000
 {"\\x65000000","\\x0d6162636465","\\x00b01b444bde0200","\\x00","\\xec91cb1681000000"} |  2 |   8064 |        1 |     64 |    502 |      0 |        0 | (0,2)  |           5 |      11010 |     24 |          |       | \x650000000d616263646500000000000000b01b444bde02000000000000000000ec91cb1681000000
 {"\\x66000000","\\x0d5858585858","\\x01cefdce0a4f0200",NULL,"\\x0000000000000000"}    |  3 |   8008 |        1 |     56 |    549 |    550 |        0 | (0,3)  |        8197 |        259 |     24 | 11101000 |       | \x660000000d585858585800000000000001cefdce0a4f02000000000000000000
(3 rows)
```

get_raw_page関数で指定テーブルの何番目のページについて、バイト列を bytea型データで取得して、それを heap_page_items関数で解析します。
さらに、heap_page_item関数が返す行データ部分（t_data）を tuple_data_split関数が列ごとに分割して配列で返してくれます。
テーブル指定 → 解析結果 ではなく、テーブルのページ指定 → バイト列 → 解析結果 という形態になっているため、テーブルのページデータを他の PostgreSQLサーバ（ただし、同アーキテクチャ、同OS、同ビルドオプション）に bytea型のデータとして持ち込んで解析することも可能です。

2行のデータに対して 3行が返っているのは、削除済み等で SELECT では返らない行も含まれるためです。また、これは 0番目ページに含まれるデータですので、複数ページにわたる、より大きなテーブルの場合には各ページについて調べることになります。

## バイナリデータを人間が読むには

pageinspectモジュールの関数を使って列ごとの物理格納データを参照できました。
しかし、できることなら、バイト列ではなく、人間に読みやすい形でデータ内容を確認したいところです。

\x0d4142434445 が 'ABCDE' というのは人によっては一目でわかるかもしれません。
また、「SET bytea_output TO escape;」とすれば出力は '\015ABCDE' になりますので一応は見分けがつきます。
しかし、'\x00f0e069ce4e0200' が '2020-08-01 12:00:00' というのは、ちょっと分かりません。

各データ型の格納されたバイト列から、SQLで扱える各データ型の値に変換する方法が、PostgreSQL機能を使って実現できないものか色々試したのですが、うまい方法が見つかりませんでした。

そこで力業です。そのための拡張モジュールを作成しました。

# [pg_pageinspect_plus](https://github.com/harukat/pg_pageinspect_plus)

[pg_pageinspect_plus](https://github.com/harukat/pg_pageinspect_plus) は、よく使われるデータ型に対して、bytea型の格納形式バイト列から、そのデータ型の値に変換する関数群を提供します。
また、pageinspect の tuple_data_split関数の出力を読み込んで、各列の値を各データ型の表示形式の文字列の配列にして返す関数 tuple_data_parse も提供します。

## インストール手順

インストール手順は標準的な PostgreSQL の拡張モジュールの導入手順通りです。
PostgreSQLのヘッダファイルがある状態（rpmパッケージからの導入なら postgresql12-devel などのパッケージが導入済み）で、PostgreSQLのコマンド群にパスが通った状態で、以下を実行します。

```shell
$ cd pg_pageinspect_plus
$ make
$ su -c 'make install'
```

開発は 13beta2 で行いましたが、比較的新しい PostgreSQL であれば動作するはずです。

## 動作させる

先ほどの tuple_data_split関数の結果をtuple_data_parse に喰わせると以下のようになります。テーブル定義からデータ型を読み取って、各列のバイト列を各データ型のテキスト表現に変換してくれます。

```sql
db=# CREATE EXTENSION pg_pageinspect_plus;
CREATE EXTENSION

db=# SELECT tuple_data_parse('public.t1'::regclass, tuple_data_split('public.t1'::regclass, t_data, t_infomask, t_infomask2, t_bits))
      FROM heap_page_items(get_raw_page('public.t1', 0));
                   tuple_data_parse                   | lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff |  t_bits  | t_oid |                                       t_data             
------------------------------------------------------+----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------+-------+------------------------------------------------------------------------------------
 {100,ABCDE,"2020-08-01 12:00:00",true,98765432}      |  1 |   8128 |        1 |     64 |    501 |      0 |        0 | (0,1)  |           5 |      11010 |     24 |          |       | \x640000000d414243444500000000000000f0e069ce4e02000100000000000000780ae30500000000
 {101,abcde,"2025-08-01 12:00:00",false,554433221100} |  2 |   8064 |        1 |     64 |    502 |      0 |        0 | (0,2)  |           5 |      11010 |     24 |          |       | \x650000000d616263646500000000000000b01b444bde02000000000000000000ec91cb1681000000
 {102,XXXXX,"2020-08-04 12:03:14.428417",NULL,0}      |  3 |   8008 |        1 |     56 |    549 |    550 |        0 | (0,3)  |        8197 |        259 |     24 | 11101000 |       | \x660000000d585858585800000000000001cefdce0a4f02000000000000000000
(3 rows)
````

これで、SELECT結果との対応付けが分かりやすくなりました。

個別にバイト列を各データ型の値に変換する例は以下の通りです。
bytea2～という関数群が提供されます。

```sql
db=# SELECT bytea2text('\x0d5858582020');
 bytea2text
------------
 XXX
(1 row)

db1=# SELECT bytea2timestamptz('\x90fbb5cbe24e0200');
     bytea2timestamptz
----------------------------
 2020-08-02 21:19:00.698+09
(1 row)

db=# SELECT bytea2interval('\x0010acd1530000000000000000000000');
 bytea2interval
----------------
 100:00:00
(1 row)

db=# SELECT bytea2float8('\x33f68845cac02340');
 bytea2float8
--------------
   9.87654321
(1 row)
```

今のところ、対応しているデータ型は、timestamp、timestamp with time zone、interval、integer（int、int4）、bigint（int8）、boolean、text、char、varchar、json、real（float4）、double precision（float8）だけです。
このほか格納形式がtext型と同じデータ型は、bytea2text関数が利用できます。
関数の一覧は本拡張モジュールの [README](https://github.com/harukat/pg_pageinspect_plus) を参照してください。