Introduction

This blog is aimed at beginners trying to learn the basics of PostgreSQL but already have some experience under their belt. For this tutorial, we will assume you have PostgreSQL correctly installed on Ubuntu. All of these steps were done using PostgreSQL 16 (development version) and Ubuntu 23.04. We’ll go over 3 different but related testing frameworks: regression, TAP, and pgTap.

First, we’ll go over regression testing. For the uninitiated, regression tests refer to tests that should be run after any changes to make sure that no new bugs are introduced into the system. This test suite should be the most comprehensive and cover all the base functionality. As a result, we can determine the correctness of our changes by running them against this test suite.

Regression Tests

The regression test suite in PostgreSQL can be run in one of two ways:

1. On a temporary server that only exists for the tests
2. On an installed server that is running before the tests start

Running

$ make check

will execute the “core” tests on a temporary server. Furthermore, we can run

$ make check-world

to run the entire test suite on the temporary server and not just the “core” tests.

Alternatively, we can issue the following command to run the tests on an existing server:

$ make checkinstall

Similar to the temporary server, there exists a “world” equivalent to run the entire test suite on an existing server:

$ make checkinstall-world

Make sure you actually have a server running on the default port otherwise, this test suite will fail.

Overall, these tests are to ensure that PostgreSQL itself is operating correctly and without error to any of the internals. So it is a good idea to run these tests after you make any changes to the Postgres source code to ensure your changes meet these baselines. The following tests have more to do with testing a PostgreSQL implementation. That is, testing to see if your schema, functions, views, etc. work correctly when analyzed at a higher level.

TAP Tests

The “test anything protocol”, is a simple text-based interface for testing modules. TAP tests for PostgreSQL use the Perl language to write tests. This testing framework is aimed at being a more flexible way to test your database when compared to regression tests.

To enable TAP tests, PostgreSQL must be compiled with the --enable-tap-tests option in the configuration script.

Running TAP Tests

Various tests can be found inside of src/test (and other directories too!) organized by what they are testing. Within these folders, we may find more folders that contain a more specific organization as to what is being tested. The folders which contain a t/ directory have TAP tests. The t/ folder contains the Perl script which describes the tests to be run. The folder containing the t/ folder should also have a Makefile which contains a rule for running the test, looking something like this:

check:
	$(prove_check)

installcheck:
	$(prove_installcheck)

We can then run the specific tests by issuing the following command in the directory of the test we want to run:

$ make check PROVE_TESTS='t/001_test1.pl t/003_test3.pl'

PROVE_TESTS accepts a space-separated list of test names searching from the directory the Makefile is contained in.

We can also pass in flags to the tests by using PROVE_FLAGS like so:

$ make check PROVE_TESTS='t/001_test1.pl t/003_test3.pl' PROVE_FLAGS='--verbose'

This will run the same tests with all of the intended output like test names and descriptions when running.

Adding TAP Tests

To add tests we simply have to follow the general structure outlined above. Your tests will likely vary greatly and may require additional files and folders. Here we will only be going through the bare minimum required to run a TAP test.

First, we have to create a directory that will contain our tests. Alternatively, we can add to another directory that may encompass similar tests.

$ mkdir example/

In this folder, we’ll add our Makefile and testing folder as well as the Perl script inside the testing folder.

$ mkdir example/t/
$ touch example/Makefile example/t/001_test.pl

Example TAP Test

For this example, we’ll make a new directory in src/test to store our tests like above

$ mkdir src/test/example/
$ mkdir src/test/example/t/
$ touch src/test/example/Makefile src/test/example/t/001_test.pl

Now let’s look at some code. This example is adapted from a test contained within Postgres, namely src/test/modules/test_misc/t/001_constraint_validation.pl

use strict;
use warnings;
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;

# Initialize a test cluster
my $node = PostgreSQL::Test::Cluster->new('primary');
$node->init();
# Turn message level up to DEBUG1 so that we get the messages we want to see
$node->append_conf('postgresql.conf', 'client_min_messages = DEBUG1');
$node->start;

# Run a SQL command and return psql's stdout
sub run_sql_command
{
	my $sql = shift;
	my $stdout;

	$node->psql(
		'postgres',
		$sql,
		stdout        => \$stdout,
		on_error_die  => 1,
		on_error_stop => 1);
	return $stdout;
}

my $output;

note "test simple insert and select";

run_sql_command(
	'create table test1 (test_a int, test_b int);
	 insert into test1 values (1, 2);');

$output = run_sql_command('select test_a from test1;');
ok($output == "1",
	'Retrieve first column');

$output = run_sql_command('select test_b from test1;');
ok($output == "2",
	'Retrieve second column');

$output = run_sql_command('select * from test1;');
ok($output == "1|2",
	'Retrieve both columns');

$node->stop('fast');

done_testing();

Running this test from src/test/example with the following command:

$ make check PROVE_TESTS='t/001_test.pl' PROVE_FLAGS='--verbose'

We get the following output:

# +++ tap check in src/test/test +++
t/001_test.pl .. 
# test simple insert and select
ok 1 - Retrieve first column
ok 2 - Retrieve second column
ok 3 - Retrieve both columns
1..3
ok
All tests successful.
Files=1, Tests=3,  1 wallclock secs ( 0.01 usr  0.01 sys +  0.81 cusr  0.15 csys =  0.98 CPU)
Result: PASS

So what does this test do?

1. Lines 7-12: Initializes the test cluster and sets up the Postgres config file to allow for more verbose debugging.
2. Line 14-27: Defines a function to run an SQL statement with psql.
3. Lines 29-47: Runs the actual tests.
   1. The call to note prints a line in the test.
   2. The function ok() verifies that the logic evaluates to true, if it does not then an error in the test is reported.
4. Lines 49-51: Clean up the testing environment by shutting down the test node and concluding the test.

While this framework is effective at creating and running tests, it requires quite a bit of setup and knowledge of both Perl and Makefiles. Next, we’ll look at a much simpler testing framework written in pure SQL.

PGTap

Now imagine instead of using Perl, we could use something a little more familiar to Postgres, like SQL! Well, that is what PGTap provides, an extension with functions to test your database directly with SQL statements.

Installing PGTap

First, we need to clone the PGTap repository

$ git clone https://github.com/theory/pgtap.git

Then to build and install simply run:

$ make
$ make install
$ make installcheck

Once you’ve verified the installation was successful we can enter into psql and create our PGTap extension:

# CREATE EXTENSION pgtap;
CREATE EXTENSION

From here we can either run an SQL script containing our tests or write our tests directly into the command line.

Using PGTap

Let’s convert the above Perl TAP test into a PGTap test

CREATE EXTENSION pgtap;
BEGIN;
SELECT plan( 3 );

CREATE TABLE test1 (test_a int, test_b int);
INSERT INTO test1 VALUES (1, 2);

SELECT is(
    ARRAY(SELECT test_a FROM test1), ARRAY[1], 'Retrieve first column'
);

SELECT is(
    ARRAY(SELECT test_b FROM test1), ARRAY[2], 'Retrieve second column'
);

SELECT row_eq(
    $$ SELECT * FROM test1 $$, ROW(1,2), 'Retrieve both columns'
);

ROLLBACK;

We can already see that this test is much shorter, and if you’re more familiar with SQL, easier to read too.

This test can be run using psql like any other query:

psql -U postgres -f test.sql

Giving us the following output:

CREATE EXTENSION
BEGIN
 plan 
------
 1..3
(1 row)

CREATE TABLE
INSERT 0 1
              is              
------------------------------
 ok 1 - Retrieve first column
(1 row)

              is               
-------------------------------
 ok 2 - Retrieve second column
(1 row)

            row_eq            
------------------------------
 ok 3 - Retrieve both columns
(1 row)

ROLLBACK

So what does this new test do?

1. Lines 1-3: We create our pgtap extension so we can utilize its functions, then we begin a transaction, and lastly we set the number of tests we plan to run to 3.
2. Lines 5-6: We create and initialize our table for testing
3. Lines 8-18: We run our actual tests
   1. SELECT is() takes as parameters, first the value we have, second what we expect, and finally an optional description
   2. SELECT row_eq() is similar to is() except rather than comparing one value it compares a row, so the first parameter is the row we have, the second is the row we expect, and lastly an optional description. Make careful note of how we wrap the values either in $$ or ROW().
4. Line 20: We rollback the transaction back to how it was before our test started.

As we can see, this testing framework is much more concise and easier to follow compared to Perl’s TAP. Using tools that you’re probably already familiar with from using Postgres, like psql and SQL, makes writing these tests very straightforward.

Conclusion

As we can see, PostgreSQL contains a multitude of frameworks for testing that both the underlying DBMS and schemas are functioning as intended. We first looked at how to run regression tests so that we can ensure our PostgreSQL installation is working after we made any modifications to the source code. After, we saw how to both run and add TAP tests so that we can test our schemas for correctness. Finally, we looked at how an extension like PGTap can make writing and running TAP tests easier for a developer. Overall, PostgreSQL has a tonne of options for tests to ensure correctness so there’s no reason you shouldn’t be adding them to your database.

References

“33.1. Running the Tests.” PostgreSQL Documentation, 11 May 2023, www.postgresql.org/docs/current/regress-run.html.

“33.2. Test Evaluation.” PostgreSQL Documentation, 14 July 2023, www.postgresql.org/docs/devel/regress-evaluation.html.

“33.4. Tap Tests.” PostgreSQL Documentation, 11 May 2023, www.postgresql.org/docs/current/regress-tap.html.

Corkindale, Toby, et al. “Test::PostgreSQL.” MetaCPAN, 2015, metacpan.org/pod/Test::PostgreSQL.

Paquier, Michael. “TAP Tests and External Modules.” Michael Paquier, 25 May 2018, paquier.xyz/postgresql-2/tap-test-modules/.

“PgTAP.” pgTAP, pgtap.org/. Accessed 14 July 2023.

Ringer, Craig. “Using the Postgresql TAP Framework in Extensions – 2ndQuadrant: Postgresql.” 2ndQuadrant, 10 Apr. 2017, www.2ndquadrant.com/en/blog/using-postgresql-tap-framework-extensions/.

“Tap Test.” pgPedia, pgpedia.info/t/tap-test.html. Accessed 14 July 2023.

Test Anything Protocol, testanything.org/. Accessed 14 July 2023.