Regression test authoring

Writing a new regression test for PostgreSQL

While trying to write a new regression test, I noticed a lack of documentation on the subject. This document is my attempt to record what I learned for future reference.

File layout

All regression test files live in pgsql/src/test/regress/ in GIT.

Schedule Files

• parallel_schedule
• serial_schedule
  • These files are parsed and processed by the pgsql/src/test/regress/pg_regress executable. These files have the following format: empty lines and lines with trailing "#" symbol are ignored, lines that start with the "test:" code word define tests to run, lines that start with the "ignore:" code word form an ignore list. When pg_regress comes to a test that is in the ignore list, it will run the test anyway, but will ignore the test's failure. You can specify only one test name per "ignore:"-line and up to 20 test names per "test:"-line. If you specify more than one name in a "test:"-line, these tests will be run in parallel mode (parallel_schedule does it this way). If there is only one test name, the test will be run in single mode (serial_schedule does it this way). parallel_schedule is used for running the make check command. serial_schedule is used for make installcheck. If you want to add a new regression test, you should add its name to both parallel_schedule and serial_schedule.

Test's SQL Code and Expected Results

• sql/
  • This directory contains the sql script which the test harness will run. The output of the regression test harness lists each file as a separate test item, so it is customary to test one "feature" per sql file
• expected/
  • This directory contains *.out files which contain the expected output for each regression script. The output file contains, in addition to the results of any queries, the contents of the sql file, including any comments.
• results/
  • This directory, while not containing any user-generated files, contains the output of the regression scripts after they run. The *.out files in here can generally be copied into the expected/ directory when writing a new test.

Unusual Tests

• input/
• output/
  • Contains *.source files which need to be preprocessed before becoming *.sql and *.out files respectively.
• data/
  • Tests which require data files (currently only copy tests, though I am currently working on a large object one as well) store them in this directory. Tests which need to access files in this directory need to take advantage of the preprocessing mechanism above and refer to the directory as @abs_srcdir@/data/, which will be replaced with the absolute path to the data directory.

Substituted variables

• @abs_srcdir@
  • the absolute path to the src/test/regress directory in the source tree
• @abs_builddir@
  • the absolute path to the src/test/regress directory in the build tree, which in VPATH builds (i.e. building outside the source tree) are different from the source tree.
• @testtablespace@
• @DLSUFFIX@

Example test

In order to illustrate how a regression test is constructed, I will create a simple (if pointless) test to show how it is done.

sql/simple.sql

This script is the actual regression test which will be run

--
-- A simple brain-dead test to show how one is written
--

-- Create a table
CREATE TABLE simple_test (a integer, b text);

-- Add a couple of rows
COPY simple_test FROM stdin;
1	foo
2	bar
3	baz
4	floob
\.

-- Select it back out in reverse order
SELECT * FROM simple_test ORDER BY a DESC;

-- remove a row
DELETE FROM simple_test WHERE length(b) > 3;

-- Select again
SELECT * FROM simple_test ORDER BY a DESC;

-- Clean up
DROP TABLE simple_test;

expected/simple.out

This file contains the expected output of the regression script above

--
-- A simple brain-dead test to show how one is written
--
-- Create a table
CREATE TABLE simple_test (a integer, b text);
-- Add a couple of rows
COPY simple_test FROM stdin;
-- Select it back out in reverse order
SELECT * FROM simple_test ORDER BY a DESC;
 a |   b   
---+-------
 4 | floob
 3 | baz
 2 | bar
 1 | foo
(4 rows)

-- remove a row
DELETE FROM simple_test WHERE length(b) > 3;
-- Select again
SELECT * FROM simple_test ORDER BY a DESC;
 a |  b  
---+-----
 3 | baz
 2 | bar
 1 | foo
(3 rows)

-- Clean up
DROP TABLE simple_test;

parallel_schedule

The simple test needs to be added to the schedule in order to be run. Find a likely group and add it to the list. I put it in "The fourth group of parallel test".

This is the cvs diff of my adding it to the group. It may no longer apply when you try it as this file may change in CVS. This is only meant as an example of putting a new test in there, so don't take it too literally.

@@ -79,7 +79,7 @@
 # ----------
 # Another group of parallel tests
 # ----------
-test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
+test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete simple
 
 # ----------
 # Another group of parallel tests

serial_schedule

This schedule is used for make installcheck. Put your test in this file in approximately the same order as the parallel_schedule for easier maintenance. Same comment about the parallel_schedule patch applies to this one.

Index: serial_schedule
===================================================================
RCS file: /home/jeremyd/local/postgres/cvsuproot/pgsql/src/test/regress/serial_schedule,v
retrieving revision 1.33
diff -c -r1.33 serial_schedule
*** serial_schedule     30 Aug 2006 23:34:22 -0000      1.33
--- serial_schedule     24 Sep 2006 23:52:05 -0000
***************
*** 76,81 ****
--- 76,82 ----
  test: hash_index
  test: update
  test: delete
+ test: simple
  test: namespace
  test: prepared_xacts
  test: privileges

Updating an existing regression test

The typical way to update an existing regression test is to write new commands into the sql/foo.sql file, run the test suite once, examine the failures; if everything is as you wanted it, copy over the results/foo.out file to expected/, then run the test suite again, and now hopefully everything passes. This works fine as long as you are not dealing with any unusual tests that are built from source/ and output/ or have variant expected files. Then you need to merge the differences into those various files. The command "merge" from the package rcs can help, e.g.,

merge output/largeobject.source expected/largeobject.out results/largeobject.out

or

merge expected/json_1.out expected/json.out results/json.out

(before copying results/json.out to expected/json.out).