はじめに
前回に引き続き、ストアドプロシージャの開発に入門していく。
ストアドプロシージャを作成したら、そのプロシージャが期待通りに動作するかをテストしたい。
なので、今回は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
https://pgtap.org/
pgTAPの特徴
- PostgreSQL専用: データベース内でSQLとして実行される
- アサーション: テーブル、関数、データの検証機能
主要な機能
機能 | 説明 |
---|---|
plan() | テスト数の宣言 |
is() | 値の等価性をテスト |
has_function() | 関数/プロシージャの存在確認 |
has_table() | テーブルの存在確認 |
finish() | テストの終了 |
リファレンスは以下
- documentation | pgtap
https://pgtap.org/documentation.html
pgTAPとpg_proveの導入
Docker環境の準備
まず、pgTAP
拡張機能をインストールするために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
拡張機能を有効化する。
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()
このプロシージャは以下の動作をする。
- 差分なしレコード:
t_data
から削除 - 差分ありレコード:
t_datalatest
を更新 - 新規レコード:
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: プロシージャの存在確認
-- テスト開始
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: 新規データの挿入
-- テスト開始
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: 差分なしデータの削除
-- テスト開始
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: 差分ありデータの更新
-- テスト開始
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値の処理
-- テスト開始
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
一括実行スクリプト
#!/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 Documentation
https://pgtap.org/TAP Specification
https://testanything.org/PostgreSQL Testing with pgTAP
https://www.postgresql.org/docs/current/regress-tap.htmlpostgresql-procedure-dev/katsuobushiFPGA | GitHub
https://github.com/katsuobushiFPGA/postgresql-procedure-devDocker上のPostgreSQLに単体テストプラグイン pgtapを導入する | Qiita
https://qiita.com/RYA234/items/5644d55634f234017378
おわりに
今回は、pgTAP
を使ったPostgreSQLストアドプロシージャの単体テストについて学んだ。
pgTAP
を使うことで以下のメリットが得られる。
- 自動化されたテスト: 手動テストの削減
- 回帰テストの実現: コード変更時の安全性確保
- ドキュメント効果: テストがプロシージャの仕様書になる
特に、データベースロジックが複雑になってくると、テストの重要性が増してくる。
今回実装したテストケースを参考に、実際にテスト駆動でストアドプロシージャ開発を進めていきたい。
次回は、パフォーマンステストや複雑なプロシージャをテストしてみたい。
どちらかというとプロシージャ単体のパフォーマンステストをしてみたいかな。