From PostgreSQL Wiki

Contents 1 Writing a new regression test for PostgreSQL 1.1 File layout 1.1.1 Commonly Used 1.1.2 Unusual Tests 1.1.2.1 Substituted variables 1.2 Example test 1.2.1 sql/simple.sql 1.2.2 expected/simple.out 1.2.3 parallel_schedule 1.2.4 serial_schedule

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 CVS.

Commonly Used

• parallel_schedule
• serial_schedule
  • These tell the regression test harness when to run your regression test. The parallel_schedule is used for running the make check command, and allows certain tests to be run in parallel. The serial_schedule is used for make installcheck and runs all tests in serial.
• 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.

Index: parallel_schedule
===================================================================
RCS file: /home/jeremyd/local/postgres/cvsuproot/pgsql/src/test/regress/parallel_schedule,v
retrieving revision 1.35
diff -c -r1.35 parallel_schedule
*** parallel_schedule   30 Aug 2006 23:34:22 -0000      1.35
--- parallel_schedule   24 Sep 2006 23:38:33 -0000
***************
*** 61,67 ****
  # ----------
  # The fourth group of parallel test
  # ----------
! 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: privileges
  test: misc
--- 61,67 ----
  # ----------
  # The fourth group of parallel test
  # ----------
! 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

  test: privileges
  test: misc

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