PostgreSQLでストアドプロシージャ開発をする その5 ~pgTAPで単体テスト~

はじめに

前回に引き続き、ストアドプロシージャの開発に入門していく。

ストアドプロシージャを作成したら、そのプロシージャが期待通りに動作するかをテストしたい。
なので、今回はpgTAPを使った単体テストについて学ぶ。

環境

Windows 11 Professional
PostgreSQL 17
Docker Desktop 4.43.2 (199162) 
pgTAP 1.3.4

GitHub

今回作成したテストコードは以下のリポジトリで公開している。

https://github.com/katsuobushiFPGA/postgresql-procedure-dev

今回対象のファイル

  • db/Dockerfile
  • tests/
  • scripts/run_tests.sh

pgTAPとは

pgTAPは、PostgreSQL用のテストフレームワーク。
TAP(Test Anything Protocol)に準拠したテスト結果を出力するため、様々なテストランナーと組み合わせて使える。

pgTAPの特徴

  • PostgreSQL専用: データベース内でSQLとして実行される
  • アサーション: テーブル、関数、データの検証機能

主要な機能

機能説明
plan()テスト数の宣言
is()値の等価性をテスト
has_function()関数/プロシージャの存在確認
has_table()テーブルの存在確認
finish()テストの終了

リファレンスは以下

pgTAPとpg_proveの導入

Docker環境の準備

まず、pgTAP拡張機能をインストールするためにDockerfileを修正する。

Dockerfile
FROM postgres:17

RUN apt-get update && \
    apt-get install -y git make gcc libkrb5-dev postgresql-server-dev-17 curl

WORKDIR /tmp
RUN git clone https://github.com/EnterpriseDB/pldebugger

WORKDIR /tmp/pldebugger
# USE_PGXS=1 をつけること既存のPostgreSQLへの拡張インストールができる
RUN USE_PGXS=1 make 
RUN USE_PGXS=1 make install

# pg_proveのインストール
RUN curl -LO http://xrl.us/cpanm \
    && chmod +x cpanm \
    && ./cpanm TAP::Parser::SourceHandler::pgTAP

# pgTAPのインストール
WORKDIR /tmp
RUN git clone https://github.com/theory/pgtap.git
WORKDIR /tmp/pgtap
RUN make
RUN make install

拡張機能の有効化

PostgreSQL起動時にpgTAP拡張機能を有効化する。

00_create_extension.sql
CREATE EXTENSION IF NOT EXISTS pldbgapi;

-- pgTAP拡張機能を有効化(テスト用)
CREATE EXTENSION IF NOT EXISTS pgtap;

環境の確認

以下のコマンドでpgTAPが正しくインストールされていることを確認できる。

-- pgTAPの確認
SELECT * FROM pg_available_extensions WHERE name = 'pgtap';

-- テスト関数の確認
\df plan
\df is
\df finish

テスト対象のプロシージャ

今回は、PL/pgSQLで2つのテーブル間の差分を検出してみるで作成した差分検出・同期プロシージャをテストする。

-- テスト対象のプロシージャ
sync_t_data_to_t_datalatest_upsert_correct()

このプロシージャは以下の動作をする。

  1. 差分なしレコード: t_dataから削除
  2. 差分ありレコード: t_datalatestを更新
  3. 新規レコード: t_datalatestに挿入

pgTAPテストの実装

テストファイル構成

tests/
├── 01_test_sync_procedure.sql  # プロシージャ存在確認
├── 02_test_sync_procedure.sql  # 新規データの挿入
├── 03_test_sync_procedure.sql  # 差分なしデータの削除
├── 04_test_sync_procedure.sql  # 差分ありデータの更新
└── 05_test_sync_procedure.sql  # NULL値の処理

テストケース1: プロシージャの存在確認

01_test_sync_procedure.sql
-- テスト開始
SELECT plan(1);

-- テスト用のデータをクリア
DELETE FROM t_data;
DELETE FROM t_datalatest;

-- テストケース1: プロシージャが存在することの確認
SELECT has_function(
    'sync_t_data_to_t_datalatest_upsert_correct',
    ARRAY[]::text[],
    'sync_t_data_to_t_datalatest_upsert_correct プロシージャが存在する'
);

-- テスト完了
SELECT finish();

テストケース2: 新規データの挿入

02_test_sync_procedure.sql
-- テスト開始
SELECT plan(5);

-- テスト用のデータをクリア
DELETE FROM t_data;
DELETE FROM t_datalatest;

-- テストケース2: 新規データの挿入テスト
INSERT INTO t_data (pkey_1, pkey_2, pkey_3, pkey_4, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10)
VALUES ('TEST1', 'B001', 'C001', 'D001', '新規1', '新規2', '新規3', '新規4', '新規5', '新規6', '新規7', '新規8', '新規9', '新規10');

-- プロシージャ実行前のテスト
SELECT is(
    (SELECT COUNT(*) FROM t_data WHERE pkey_1 = 'TEST1' AND pkey_2 = 'B001' AND pkey_3 = 'C001' AND pkey_4 = 'D001'),
    1::bigint,
    'テスト前: t_dataに新規レコードが存在する'
);

SELECT is(
    (SELECT COUNT(*) FROM t_datalatest WHERE pkey_1 = 'TEST1' AND pkey_2 = 'B001' AND pkey_3 = 'C001' AND pkey_4 = 'D001'),
    0::bigint,
    'テスト前: t_datalatest に該当レコードが存在しない'
);

-- プロシージャ実行
CALL sync_t_data_to_t_datalatest_upsert_correct();

-- 新規挿入のテスト
SELECT is(
    (SELECT COUNT(*) FROM t_datalatest WHERE pkey_1 = 'TEST1' AND pkey_2 = 'B001' AND pkey_3 = 'C001' AND pkey_4 = 'D001'),
    1::bigint,
    'テスト後: t_datalatest に新規レコードが挿入された'
);

SELECT is(
    (SELECT column_1 FROM t_datalatest WHERE pkey_1 = 'TEST1' AND pkey_2 = 'B001' AND pkey_3 = 'C001' AND pkey_4 = 'D001'),
    '新規1',
    'テスト後: 挿入されたデータが正しい'
);

SELECT is(
    (SELECT COUNT(*) FROM t_data WHERE pkey_1 = 'TEST1' AND pkey_2 = 'B001' AND pkey_3 = 'C001' AND pkey_4 = 'D001'),
    1::bigint,
    'テスト後: 新規データはt_dataから削除されていない'
);

-- テスト完了
SELECT finish();

テストケース3: 差分なしデータの削除

03_test_sync_procedure.sql
-- テスト開始
SELECT plan(5);

-- テスト用のデータをクリア
DELETE FROM t_data;
DELETE FROM t_datalatest;

-- テストケース3: 差分なしデータの削除テスト
INSERT INTO t_data (pkey_1, pkey_2, pkey_3, pkey_4, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10)
VALUES ('TEST2', 'B002', 'C002', 'D002', '同一1', '同一2', '同一3', '同一4', '同一5', '同一6', '同一7', '同一8', '同一9', '同一10');

INSERT INTO t_datalatest (pkey_1, pkey_2, pkey_3, pkey_4, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10)
VALUES ('TEST2', 'B002', 'C002', 'D002', '同一1', '同一2', '同一3', '同一4', '同一5', '同一6', '同一7', '同一8', '同一9', '同一10');

-- プロシージャ実行前のテスト
SELECT is(
    (SELECT COUNT(*) FROM t_data WHERE pkey_1 = 'TEST2' AND pkey_2 = 'B002' AND pkey_3 = 'C002' AND pkey_4 = 'D002'),
    1::bigint,
    'テスト前: t_dataに同一レコードが存在する'
);

SELECT is(
    (SELECT COUNT(*) FROM t_datalatest WHERE pkey_1 = 'TEST2' AND pkey_2 = 'B002' AND pkey_3 = 'C002' AND pkey_4 = 'D002'),
    1::bigint,
    'テスト前: t_datalatest に同一レコードが存在する'
);

-- データカラムが完全に一致していることを確認
SELECT is(
    (SELECT COALESCE(column_1, '') || '|' || COALESCE(column_2, '') || '|' || 
            COALESCE(column_3, '') || '|' || COALESCE(column_4, '') || '|' || 
            COALESCE(column_5, '') || '|' || COALESCE(column_6, '') || '|' || 
            COALESCE(column_7, '') || '|' || COALESCE(column_8, '') || '|' || 
            COALESCE(column_9, '') || '|' || COALESCE(column_10, '')
     FROM t_data WHERE pkey_1 = 'TEST2' AND pkey_2 = 'B002' AND pkey_3 = 'C002' AND pkey_4 = 'D002'),
    (SELECT COALESCE(column_1, '') || '|' || COALESCE(column_2, '') || '|' || 
            COALESCE(column_3, '') || '|' || COALESCE(column_4, '') || '|' || 
            COALESCE(column_5, '') || '|' || COALESCE(column_6, '') || '|' || 
            COALESCE(column_7, '') || '|' || COALESCE(column_8, '') || '|' || 
            COALESCE(column_9, '') || '|' || COALESCE(column_10, '')
     FROM t_datalatest WHERE pkey_1 = 'TEST2' AND pkey_2 = 'B002' AND pkey_3 = 'C002' AND pkey_4 = 'D002'),
    'テスト前: t_dataとt_datalatest の全データカラムが完全に一致している'
);

-- プロシージャ実行
CALL sync_t_data_to_t_datalatest_upsert_correct();

-- 差分なし削除のテスト
SELECT is(
    (SELECT COUNT(*) FROM t_data WHERE pkey_1 = 'TEST2' AND pkey_2 = 'B002' AND pkey_3 = 'C002' AND pkey_4 = 'D002'),
    0::bigint,
    'テスト後: 差分なしレコードがt_dataから削除された'
);

SELECT is(
    (SELECT COUNT(*) FROM t_datalatest WHERE pkey_1 = 'TEST2' AND pkey_2 = 'B002' AND pkey_3 = 'C002' AND pkey_4 = 'D002'),
    1::bigint,
    'テスト後: t_datalatest のレコードは残っている'
);

-- テスト完了
SELECT finish();

テストケース4: 差分ありデータの更新

04_test_sync_procedure.sql
-- テスト開始
SELECT plan(4);

-- テスト用のデータをクリア
DELETE FROM t_data;
DELETE FROM t_datalatest;

-- テストケース4: 差分ありデータの更新テスト
INSERT INTO t_data (pkey_1, pkey_2, pkey_3, pkey_4, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10)
VALUES ('TEST3', 'B003', 'C003', 'D003', '更新後1', '更新後2', '更新後3', '更新後4', '更新後5', '更新後6', '更新後7', '更新後8', '更新後9', '更新後10');

INSERT INTO t_datalatest (pkey_1, pkey_2, pkey_3, pkey_4, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10)
VALUES ('TEST3', 'B003', 'C003', 'D003', '更新前1', '更新前2', '更新前3', '更新前4', '更新前5', '更新前6', '更新前7', '更新前8', '更新前9', '更新前10');

-- プロシージャ実行前のテスト
SELECT is(
    (SELECT COUNT(*) FROM t_data WHERE pkey_1 = 'TEST3' AND pkey_2 = 'B003' AND pkey_3 = 'C003' AND pkey_4 = 'D003'),
    1::bigint,
    'テスト前: t_data に差分ありレコードが存在する'
);

-- 事前にデータが異なることを確認
SELECT is(
    (SELECT COALESCE(column_1, '') || '|' || COALESCE(column_2, '') || '|' || 
            COALESCE(column_3, '') || '|' || COALESCE(column_4, '') || '|' || 
            COALESCE(column_5, '') || '|' || COALESCE(column_6, '') || '|' || 
            COALESCE(column_7, '') || '|' || COALESCE(column_8, '') || '|' || 
            COALESCE(column_9, '') || '|' || COALESCE(column_10, '')
     FROM t_datalatest WHERE pkey_1 = 'TEST3' AND pkey_2 = 'B003' AND pkey_3 = 'C003' AND pkey_4 = 'D003'),
    '更新前1|更新前2|更新前3|更新前4|更新前5|更新前6|更新前7|更新前8|更新前9|更新前10',
    'テスト前: t_datalatest に古いデータが存在する(全カラム確認)'
);

-- プロシージャ実行
CALL sync_t_data_to_t_datalatest_upsert_correct();

-- 差分あり更新のテスト
SELECT is(
    (SELECT COALESCE(column_1, '') || '|' || COALESCE(column_2, '') || '|' || 
            COALESCE(column_3, '') || '|' || COALESCE(column_4, '') || '|' || 
            COALESCE(column_5, '') || '|' || COALESCE(column_6, '') || '|' || 
            COALESCE(column_7, '') || '|' || COALESCE(column_8, '') || '|' || 
            COALESCE(column_9, '') || '|' || COALESCE(column_10, '')
     FROM t_datalatest WHERE pkey_1 = 'TEST3' AND pkey_2 = 'B003' AND pkey_3 = 'C003' AND pkey_4 = 'D003'),
    '更新後1|更新後2|更新後3|更新後4|更新後5|更新後6|更新後7|更新後8|更新後9|更新後10',
    'テスト後: t_datalatest の全カラム(column_1~column_10)が正しく更新された'
);

SELECT is(
    (SELECT COUNT(*) FROM t_data WHERE pkey_1 = 'TEST3' AND pkey_2 = 'B003' AND pkey_3 = 'C003' AND pkey_4 = 'D003'),
    1::bigint,
    'テスト後: 差分ありレコードはt_dataから削除されていない'
);

-- テスト完了
SELECT finish();

テストケース5: NULL値の処理

05_test_sync_procedure.sql
-- テスト開始
SELECT plan(1);

-- テスト用のデータをクリア
DELETE FROM t_data;
DELETE FROM t_datalatest;

-- テストケース5: NULLデータの処理テスト
INSERT INTO t_data (pkey_1, pkey_2, pkey_3, pkey_4, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10)
VALUES ('TEST4', 'B004', 'C004', 'D004', NULL, 'NULL2', NULL, 'NULL4', NULL, 'NULL6', NULL, 'NULL8', NULL, 'NULL10');

INSERT INTO t_datalatest (pkey_1, pkey_2, pkey_3, pkey_4, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10)
VALUES ('TEST4', 'B004', 'C004', 'D004', NULL, 'NULL2', NULL, 'NULL4', NULL, 'NULL6', NULL, 'NULL8', NULL, 'NULL10');

-- プロシージャ実行
CALL sync_t_data_to_t_datalatest_upsert_correct();

-- NULL値の同一判定テスト
SELECT is(
    (SELECT COUNT(*) FROM t_data WHERE pkey_1 = 'TEST4' AND pkey_2 = 'B004' AND pkey_3 = 'C004' AND pkey_4 = 'D004'),
    0::bigint,
    'テスト後: NULL値を含む同一レコードがt_dataから削除された'
);

-- テスト完了
SELECT finish();

テストの実行方法

個別テストファイル実行

# Docker環境でのテスト実行
docker-compose exec db psql -U postgres -d postgres -f /tests/01_test_sync_procedure.sql
docker-compose exec db psql -U postgres -d postgres -f /tests/02_test_sync_procedure.sql
docker-compose exec db psql -U postgres -d postgres -f /tests/03_test_sync_procedure.sql
docker-compose exec db psql -U postgres -d postgres -f /tests/04_test_sync_procedure.sql
docker-compose exec db psql -U postgres -d postgres -f /tests/05_test_sync_procedure.sql

pg_proveを使った実行

# より詳細なテスト結果の表示
docker-compose exec db pg_prove -U postgres -d postgres /tests/01_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/02_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/03_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/04_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/05_test_sync_procedure.sql

一括実行スクリプト

run_tests.sh
#!/bin/bash

echo "=== pgTAPテスト実行開始 ==="

# PostgreSQLコンテナでテストを実行
docker-compose exec db pg_prove -U postgres -d postgres /tests/01_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/02_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/03_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/04_test_sync_procedure.sql
docker-compose exec db pg_prove -U postgres -d postgres /tests/05_test_sync_procedure.sql

echo "=== pgTAPテスト実行完了 ==="

テスト結果の見方

成功例

# 01_test_sync_procedure.sql
1..1
ok 1 - sync_t_data_to_t_datalatest_upsert_correct プロシージャが存在する

# 02_test_sync_procedure.sql
1..5
ok 1 - テスト前: t_dataに新規レコードが存在する
ok 2 - テスト前: t_datalatest に該当レコードが存在しない
ok 3 - テスト後: t_datalatest に新規レコードが挿入された
ok 4 - テスト後: 挿入されたデータが正しい
ok 5 - テスト後: 新規データはt_dataから削除されていない

失敗例

# 03_test_sync_procedure.sql
1..5
ok 1 - テスト前: t_dataに同一レコードが存在する
ok 2 - テスト前: t_datalatest に同一レコードが存在する
not ok 3 - テスト前: t_dataとt_datalatest の全データカラムが完全に一致している
#   Failed test 3: "テスト前: t_dataとt_datalatest の全データカラムが完全に一致している"
#          got: "同一1|同一2|同一3|同一4|同一5|同一6|同一7|同一8|同一9|同一10"
#     expected: "同一1|同一2|同一3|同一4|同一5|同一6|同一7|同一8|同一9|異なる10"

失敗時は期待値と実際の値が表示されるため、問題を特定しやすい。

Tips

複数カラムの一括検証

データカラムの一致確認には連結文字列を使用する。

-- 全カラムの連結による比較
SELECT is(
    (SELECT COALESCE(column_1, '') || '|' || COALESCE(column_2, '') || '|' || 
            COALESCE(column_3, '') || '|' || COALESCE(column_4, '') || '|' || 
            COALESCE(column_5, '') || '|' || COALESCE(column_6, '') || '|' || 
            COALESCE(column_7, '') || '|' || COALESCE(column_8, '') || '|' || 
            COALESCE(column_9, '') || '|' || COALESCE(column_10, '')
     FROM t_data WHERE ...),
    '期待値1|期待値2|期待値3|期待値4|期待値5|期待値6|期待値7|期待値8|期待値9|期待値10',
    'テスト: 全データカラムが期待値と一致している'
);

NULL値の考慮

COALESCE関数を使用してNULL値を空文字に変換することで、比較を安全に行える。

-- NULL安全な比較
SELECT is(
    COALESCE(column_1, ''),
    COALESCE('期待値', ''),
    'NULL値を考慮した比較テスト'
);

エラーケースのテスト

-- エラーケースのテスト例
SELECT throws_ok(
    'CALL non_existent_procedure()',
    '42883',
    'function non_existent_procedure() does not exist',
    '存在しないプロシージャの呼び出しでエラーが発生する'
);

トラブルシューティング

pgTAP拡張が見つからない場合

ERROR: extension "pgtap" is not available

解決方法:

  • Dockerイメージの再ビルドが必要
  • docker-compose build --no-cache db

テーブルが存在しない場合

ERROR: relation "t_data" does not exist

解決方法:

  • 初期化スクリプトの実行順序を確認
  • 07_create_sync_tables.sqlが先に実行されているか確認

プロシージャが見つからない場合

ERROR: function sync_t_data_to_t_datalatest_upsert_correct() does not exist

解決方法:

  • 08_sync_procedure.sqlが正しく実行されているか確認
  • initdbディレクトリ内のファイル順序を確認

参考

おわりに

今回は、pgTAPを使ったPostgreSQLストアドプロシージャの単体テストについて学んだ。

pgTAPを使うことで以下のメリットが得られる。

  • 自動化されたテスト: 手動テストの削減
  • 回帰テストの実現: コード変更時の安全性確保
  • ドキュメント効果: テストがプロシージャの仕様書になる

特に、データベースロジックが複雑になってくると、テストの重要性が増してくる。
今回実装したテストケースを参考に、実際にテスト駆動でストアドプロシージャ開発を進めていきたい。

次回は、パフォーマンステストや複雑なプロシージャをテストしてみたい。
どちらかというとプロシージャ単体のパフォーマンステストをしてみたいかな。

Hugo で構築されています。
テーマ StackJimmy によって設計されています。