Testing is a fundamental part of the postgresql development. Whether you are contributing to postgres itself or developing an extension, every change should be verified to ensure that existing functionality continues to work as expected.
Postgres provides multiple testing frameworks for different purposes. SQL regression tests verify sql output against expected results, isolation tests validate concurrent behavior, and TAP (Test Anything Protocol) tests are designed for scenarios that require controlling an actual PostgreSQL server. TAP tests make it possible to start and stop database clusters, modify configuration files, restart servers, execute SQL commands, and verify server behavior under different conditions.
This article demonstrates how to run TAP tests for the pg_stat_statements extension. It explains where TAP tests are located, how they are executed, and what the sample test is validating.
Check the contents of the pg_stat_statements extension in postgres source code like this.
ls postgres/postgres_19/contrib/pg_stat_statements
Result:
expected pg_stat_statements--1.10--1.11.sql pg_stat_statements--1.2--1.3.sql pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.9--1.10.sql pg_stat_statements.o
Makefile pg_stat_statements--1.11--1.12.sql pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.6--1.7.sql pg_stat_statements.c pg_stat_statements.so
meson.build pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.4--1.5.sql pg_stat_statements--1.7--1.8.sql pg_stat_statements.conf sql
pg_stat_statements--1.0--1.1.sql pg_stat_statements--1.12--1.13.sql pg_stat_statements--1.4.sql pg_stat_statements--1.8--1.9.sql pg_stat_statements.control t
At the end, we can see a folder named t.
Now, check the contents inside the folder named t like this.
ls postgres/postgres_19/contrib/pg_stat_statements/t
Result:
010_restart.pl
The .pl file is a perl script related to the testing of the pg_stat_statements extension.
Check the contents of this perl file like this.
cat t/010_restart.pl
Result:
# Copyright (c) 2023-2026, PostgreSQL Global Development Group
# Tests for checking that pg_stat_statements contents are preserved
# across restarts.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
my $node = PostgreSQL::Test::Cluster->new('main');
$node->init;
$node->append_conf('postgresql.conf',
"shared_preload_libraries = 'pg_stat_statements'");
$node->start;
$node->safe_psql('postgres', 'CREATE EXTENSION pg_stat_statements');
$node->safe_psql('postgres', 'CREATE TABLE t1 (a int)');
$node->safe_psql('postgres', 'SELECT a FROM t1');
is( $node->safe_psql(
'postgres',
"SELECT query FROM pg_stat_statements WHERE query NOT LIKE '%pg_stat_statements%' ORDER BY query"
),
"CREATE TABLE t1 (a int)\nSELECT a FROM t1",
'pg_stat_statements populated');
$node->restart;
is( $node->safe_psql(
'postgres',
"SELECT query FROM pg_stat_statements WHERE query NOT LIKE '%pg_stat_statements%' ORDER BY query"
),
"CREATE TABLE t1 (a int)\nSELECT a FROM t1",
'pg_stat_statements data kept across restart');
$node->append_conf('postgresql.conf', "pg_stat_statements.save = false");
$node->reload;
$node->restart;
is( $node->safe_psql(
'postgres',
"SELECT count(*) FROM pg_stat_statements WHERE query NOT LIKE '%pg_stat_statements%'"
),
'0',
'pg_stat_statements data not kept across restart with .save=false');
$node->stop;
done_testing();
Now, run the test for pg_stat_statements like this.
cybrosys@cybrosys:~/postgres/postgres_19$ make -C contrib/pg_stat_statements check
Result:
make: Entering directory '/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements'make -C ../../src/test/regress pg_regressmake[1]: Entering directory '/home/cybrosys/postgres/postgres_19/src/test/regress'make -C ../../../src/port allmake[2]: Entering directory '/home/cybrosys/postgres/postgres_19/src/port'make[2]: Nothing to be done for 'all'.make[2]: Leaving directory '/home/cybrosys/postgres/postgres_19/src/port'make -C ../../../src/common allmake[2]: Entering directory '/home/cybrosys/postgres/postgres_19/src/common'make[2]: Nothing to be done for 'all'.make[2]: Leaving directory '/home/cybrosys/postgres/postgres_19/src/common'make[1]: Leaving directory '/home/cybrosys/postgres/postgres_19/src/test/regress'make -C ../../src/test/isolation allmake[1]: Entering directory '/home/cybrosys/postgres/postgres_19/src/test/isolation'make -C ../../../src/interfaces/libpq allmake[2]: Entering directory '/home/cybrosys/postgres/postgres_19/src/interfaces/libpq'make -C ../../../src/port allmake[3]: Entering directory '/home/cybrosys/postgres/postgres_19/src/port'make[3]: Nothing to be done for 'all'.make[3]: Leaving directory '/home/cybrosys/postgres/postgres_19/src/port'make -C ../../../src/common allmake[3]: Entering directory '/home/cybrosys/postgres/postgres_19/src/common'make[3]: Nothing to be done for 'all'.make[3]: Leaving directory '/home/cybrosys/postgres/postgres_19/src/common'make[2]: Leaving directory '/home/cybrosys/postgres/postgres_19/src/interfaces/libpq'make -C ../../../src/port all
make -C contrib/pg_stat_statements check changes to the contrib/pg_stat_statements directory and run the tests for the pg_stat_statements extension.
It builds the extension if needed, starts a temporary Postgres server, installs the extension in the temporary database, executes the test cases, compares the results with the expected output, and reports whether the tests passed or failed. This command is mainly used by developers to verify that changes to the extension have not introduced any problems.
Now at the end of the compilation message, you can see the result of the regression tests like this.
# +++ regress check in contrib/pg_stat_statements +++
# initializing database system by copying initdb template
# using temp instance on port 58928 with PID 434996
ok 1 - select 55 ms
ok 2 - dml 20 ms
ok 3 - cursors 12 ms
ok 4 - utility 65 ms
ok 5 - level_tracking 68 ms
ok 6 - planning 12 ms
ok 7 - user_activity 14 ms
ok 8 - wal 12 ms
ok 9 - entry_timestamp 12 ms
ok 10 - privileges 12 ms
ok 11 - extended 15 ms
ok 12 - parallel 17 ms
ok 13 - plancache 16 ms
ok 14 - squashing 41 ms
ok 15 - cleanup 7 ms
ok 16 - oldextversions 46 ms
1..16
# All 16 tests passed.
TAP tests not enabled. Try configuring with --enable-tap-tests
make: Leaving directory '/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements'
The output shows that the regression tests for the pg_stat_statements extension completed successfully. A temporary PostgreSQL server was created specifically for testing, and the extension was validated using 16 regression test cases covering features such as query execution, data modification, cursors, utility commands, planning, WAL statistics, user activity, privileges, parallel execution, plan caching, and cleanup.
Every test passed without errors, confirming that the extension is functioning correctly. The final message notes that TAP tests were not executed because PostgreSQL was not built with TAP test support enabled.
make distclean removes all files generated during the build process and restores the source tree to a clean state, as it was immediately after extracting or cloning the source code. It deletes compiled object files, executables, generated configuration files, and other build artifacts, allowing you to perform a fresh configuration and rebuild from scratch.
make distclean
Now, configure postgres from start by including the flag named --enable-tap-tests.
PostgreSQL builds TAP support only when configured with --enable-tap-tests, because it depends on additional perl testing modules. Without this option, regression tests run but skip TAP tests.
./configure --prefix=$(pwd) --enable-cassert --enable-debug --enable-tap-tests
Now, compile and install postgres source code like this.
make -j $(nproc) && sudo make install
Now, use the command below to execute the tap test for the pg_stat_statements extension and check the result.
cybrosys@cybrosys:~/postgres/postgres_19$ make -C contrib/pg_stat_statements check
Result:
# +++ regress check in contrib/pg_stat_statements +++
# initializing database system by copying initdb template
# using temp instance on port 58928 with PID 447251
ok 1 - select 57 ms
ok 2 - dml 20 ms
ok 3 - cursors 13 ms
ok 4 - utility 68 ms
ok 5 - level_tracking 65 ms
ok 6 - planning 11 ms
ok 7 - user_activity 14 ms
ok 8 - wal 10 ms
ok 9 - entry_timestamp 12 ms
ok 10 - privileges 13 ms
ok 11 - extended 13 ms
ok 12 - parallel 16 ms
ok 13 - plancache 18 ms
ok 14 - squashing 41 ms
ok 15 - cleanup 7 ms
ok 16 - oldextversions 44 ms
1..16
# All 16 tests passed.
echo "# +++ tap check in contrib/pg_stat_statements +++" && rm -rf '/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements'/tmp_check && /usr/bin/mkdir -p '/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements'/tmp_check && cd . && TESTLOGDIR='/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements/tmp_check/log' TESTDATADIR='/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements/tmp_check' PATH="/home/cybrosys/postgres/postgres_19/tmp_install/home/cybrosys/postgres/postgres_19/bin:/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements:$PATH" LD_LIBRARY_PATH="/home/cybrosys/postgres/postgres_19/tmp_install/home/cybrosys/postgres/postgres_19/lib" INITDB_TEMPLATE='/home/cybrosys/postgres/postgres_19'/tmp_install/initdb-template PGPORT='65432' top_builddir='/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements/../..' PG_REGRESS='/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements/../../src/test/regress/pg_regress' share_contrib_dir='/home/cybrosys/postgres/postgres_19/tmp_install/home/cybrosys/postgres/postgres_19/share/extension' /usr/bin/prove -I ../../src/test/perl/ -I . t/*.pl
# +++ tap check in contrib/pg_stat_statements +++
t/010_restart.pl .. ok
All tests successful.
Files=1, Tests=3, 1 wallclock secs ( 0.02 usr 0.00 sys + 0.07 cusr 0.15 csys = 0.24 CPU)
Result: PASS
make: Leaving directory '/home/cybrosys/postgres/postgres_19/contrib/pg_stat_statements'
The output shows that both the regression tests and TAP tests for the pg_stat_statements extension completed successfully without any errors or failures. The regression test suite created an instance of postgres and ran 16 test cases. These test cases covered features of the extension, and every single one passed without issues.
After the regression tests finished, the TAP test suite started. The 010_restart.pl test script ran three test cases. All three passed. The final result was a PASS, which confirms that the extension works correctly under both regression and TAP testing. This means the implementation is behaving as expected and is stable.
For extension developers and postgres contributors, learning TAP testing is important. Many advanced features cannot be tested thoroughly using SQL regression tests. Using TAP tests helps the extensions and server features stay reliable. This includes changes in configuration and different stages in the life cycle of the postgres server. TAP testing adds a layer of confidence in how the system performs over time.