1 <!-- doc/src/sgml/regress.sgml -->
4 <title>Regression Tests</title>
6 <indexterm zone="regress">
7 <primary>regression tests</primary>
10 <indexterm zone="regress">
11 <primary>test</primary>
15 The regression tests are a comprehensive set of tests for the SQL
16 implementation in <productname>PostgreSQL</productname>. They test
17 standard SQL operations as well as the extended capabilities of
18 <productname>PostgreSQL</productname>.
21 <sect1 id="regress-run">
22 <title>Running the Tests</title>
25 The regression tests can be run against an already installed and
26 running server, or using a temporary installation within the build
27 tree. Furthermore, there is a <quote>parallel</quote> and a
28 <quote>sequential</quote> mode for running the tests. The
29 sequential method runs each test script alone, while the
30 parallel method starts up multiple server processes to run groups
31 of tests in parallel. Parallel testing adds confidence that
32 interprocess communication and locking are working correctly.
36 <title>Running the Tests Against a Temporary Installation</title>
39 To run the parallel regression tests after building but before installation,
44 in the top-level directory. (Or you can change to
45 <filename>src/test/regress</filename> and run the command there.)
46 At the end you should see something like:
49 =======================
51 =======================
54 or otherwise a note about which tests failed. See <xref
55 linkend="regress-evaluation"/> below before assuming that a
56 <quote>failure</quote> represents a serious problem.
60 Because this test method runs a temporary server, it will not work
61 if you did the build as the root user, since the server will not start as
62 root. Recommended procedure is not to do the build as root, or else to
63 perform testing after completing the installation.
67 If you have configured <productname>PostgreSQL</productname> to install
68 into a location where an older <productname>PostgreSQL</productname>
69 installation already exists, and you perform <literal>make check</literal>
70 before installing the new version, you might find that the tests fail
71 because the new programs try to use the already-installed shared
72 libraries. (Typical symptoms are complaints about undefined symbols.)
73 If you wish to run the tests before overwriting the old installation,
74 you'll need to build with <literal>configure --disable-rpath</literal>.
75 It is not recommended that you use this option for the final installation,
80 The parallel regression test starts quite a few processes under your
81 user ID. Presently, the maximum concurrency is twenty parallel test
82 scripts, which means forty processes: there's a server process and a
83 <application>psql</application> process for each test script.
84 So if your system enforces a per-user limit on the number of processes,
85 make sure this limit is at least fifty or so, else you might get
86 random-seeming failures in the parallel test. If you are not in
87 a position to raise the limit, you can cut down the degree of parallelism
88 by setting the <literal>MAX_CONNECTIONS</literal> parameter. For example:
90 make MAX_CONNECTIONS=10 check
92 runs no more than ten tests concurrently.
97 <title>Running the Tests Against an Existing Installation</title>
100 To run the tests after installation (see <xref linkend="installation"/>),
101 initialize a data area and start the
102 server as explained in <xref linkend="runtime"/>, then type:
106 or for a parallel test:
108 make installcheck-parallel
110 The tests will expect to contact the server at the local host and the
111 default port number, unless directed otherwise by <envar>PGHOST</envar> and
112 <envar>PGPORT</envar> environment variables. The tests will be run in a
113 database named <literal>regression</literal>; any existing database by this name
118 The tests will also transiently create some cluster-wide objects, such as
119 roles and tablespaces. These objects will have names beginning with
120 <literal>regress_</literal>. Beware of using <literal>installcheck</literal>
121 mode in installations that have any actual users or tablespaces named
127 <title>Additional Test Suites</title>
130 The <literal>make check</literal> and <literal>make installcheck</literal> commands
131 run only the <quote>core</quote> regression tests, which test built-in
132 functionality of the <productname>PostgreSQL</productname> server. The source
133 distribution also contains additional test suites, most of them having
134 to do with add-on functionality such as optional procedural languages.
138 To run all test suites applicable to the modules that have been selected
139 to be built, including the core tests, type one of these commands at the
140 top of the build tree:
143 make installcheck-world
145 These commands run the tests using temporary servers or an
146 already-installed server, respectively, just as previously explained
147 for <literal>make check</literal> and <literal>make installcheck</literal>. Other
148 considerations are the same as previously explained for each method.
149 Note that <literal>make check-world</literal> builds a separate temporary
150 installation tree for each tested module, so it requires a great deal
151 more time and disk space than <literal>make installcheck-world</literal>.
155 Alternatively, you can run individual test suites by typing
156 <literal>make check</literal> or <literal>make installcheck</literal> in the appropriate
157 subdirectory of the build tree. Keep in mind that <literal>make
158 installcheck</literal> assumes you've installed the relevant module(s), not
159 only the core server.
163 The additional tests that can be invoked this way include:
169 Regression tests for optional procedural languages (other than
170 <application>PL/pgSQL</application>, which is tested by the core tests).
171 These are located under <filename>src/pl</filename>.
176 Regression tests for <filename>contrib</filename> modules,
177 located under <filename>contrib</filename>.
178 Not all <filename>contrib</filename> modules have tests.
183 Regression tests for the ECPG interface library,
184 located in <filename>src/interfaces/ecpg/test</filename>.
189 Tests stressing behavior of concurrent sessions,
190 located in <filename>src/test/isolation</filename>.
195 Tests of client programs under <filename>src/bin</filename>. See
196 also <xref linkend="regress-tap"/>.
202 When using <literal>installcheck</literal> mode, these tests will destroy any
203 existing databases named <literal>pl_regression</literal>,
204 <literal>contrib_regression</literal>, <literal>isolation_regression</literal>,
205 <literal>ecpg1_regression</literal>, or <literal>ecpg2_regression</literal>, as well as
206 <literal>regression</literal>.
210 The TAP-based tests are run only when PostgreSQL was configured with the
211 option <option>--enable-tap-tests</option>. This is recommended for
212 development, but can be omitted if there is no suitable Perl installation.
216 Some test suites are not run by default, either because they are not secure
217 to run on a multiuser system or because they require special software. You
218 can decide which test suites to run additionally by setting the
219 <command>make</command> or environment variable
220 <varname>PG_TEST_EXTRA</varname> to a whitespace-separated list, for
223 make check-world PG_TEST_EXTRA='kerberos ldap ssl'
225 The following values are currently supported:
228 <term><literal>kerberos</literal></term>
231 Runs the test suite under <filename>src/test/kerberos</filename>. This
232 requires an MIT Kerberos installation and opens TCP/IP listen sockets.
238 <term><literal>ldap</literal></term>
241 Runs the test suite under <filename>src/test/ldap</filename>. This
242 requires an <productname>OpenLDAP</productname> installation and opens
243 TCP/IP listen sockets.
249 <term><literal>ssl</literal></term>
252 Runs the test suite under <filename>src/test/ssl</filename>. This opens TCP/IP listen sockets.
258 Tests for features that are not supported by the current build
259 configuration are not run even if they are mentioned in
260 <varname>PG_TEST_EXTRA</varname>.
265 <title>Locale and Encoding</title>
268 By default, tests using a temporary installation use the
269 locale defined in the current environment and the corresponding
270 database encoding as determined by <command>initdb</command>. It
271 can be useful to test different locales by setting the appropriate
272 environment variables, for example:
275 make check LC_COLLATE=en_US.utf8 LC_CTYPE=fr_CA.utf8
277 For implementation reasons, setting <envar>LC_ALL</envar> does not
278 work for this purpose; all the other locale-related environment
283 When testing against an existing installation, the locale is
284 determined by the existing database cluster and cannot be set
285 separately for the test run.
289 You can also choose the database encoding explicitly by setting
290 the variable <envar>ENCODING</envar>, for example:
292 make check LANG=C ENCODING=EUC_JP
294 Setting the database encoding this way typically only makes sense
295 if the locale is C; otherwise the encoding is chosen automatically
296 from the locale, and specifying an encoding that does not match
297 the locale will result in an error.
301 The database encoding can be set for tests against either a temporary or
302 an existing installation, though in the latter case it must be
303 compatible with the installation's locale.
308 <title>Extra Tests</title>
311 The core regression test suite contains a few test files that are not
312 run by default, because they might be platform-dependent or take a
313 very long time to run. You can run these or other extra test
314 files by setting the variable <envar>EXTRA_TESTS</envar>. For
315 example, to run the <literal>numeric_big</literal> test:
317 make check EXTRA_TESTS=numeric_big
319 To run the collation tests:
321 make check EXTRA_TESTS='collate.icu.utf8 collate.linux.utf8' LANG=en_US.utf8
323 The <literal>collate.linux.utf8</literal> test works only on Linux/glibc
324 platforms. The <literal>collate.icu.utf8</literal> test only works when
325 support for ICU was built. Both tests will only succeed when run in a
326 database that uses UTF-8 encoding.
331 <title>Testing Hot Standby</title>
334 The source distribution also contains regression tests for the static
335 behavior of Hot Standby. These tests require a running primary server
336 and a running standby server that is accepting new WAL changes from the
337 primary (using either file-based log shipping or streaming replication).
338 Those servers are not automatically created for you, nor is replication
339 setup documented here. Please check the various sections of the
340 documentation devoted to the required commands and related issues.
344 To run the Hot Standby tests, first create a database
345 called <literal>regression</literal> on the primary:
347 psql -h primary -c "CREATE DATABASE regression"
349 Next, run the preparatory script
350 <filename>src/test/regress/sql/hs_primary_setup.sql</filename>
351 on the primary in the regression database, for example:
353 psql -h primary -f src/test/regress/sql/hs_primary_setup.sql regression
355 Allow these changes to propagate to the standby.
359 Now arrange for the default database connection to be to the standby
360 server under test (for example, by setting the <envar>PGHOST</envar> and
361 <envar>PGPORT</envar> environment variables).
362 Finally, run <literal>make standbycheck</literal> in the regression directory:
370 Some extreme behaviors can also be generated on the primary using the
371 script <filename>src/test/regress/sql/hs_primary_extremes.sql</filename>
372 to allow the behavior of the standby to be tested.
377 <sect1 id="regress-evaluation">
378 <title>Test Evaluation</title>
381 Some properly installed and fully functional
382 <productname>PostgreSQL</productname> installations can
383 <quote>fail</quote> some of these regression tests due to
384 platform-specific artifacts such as varying floating-point representation
385 and message wording. The tests are currently evaluated using a simple
386 <command>diff</command> comparison against the outputs
387 generated on a reference system, so the results are sensitive to
388 small system differences. When a test is reported as
389 <quote>failed</quote>, always examine the differences between
390 expected and actual results; you might find that the
391 differences are not significant. Nonetheless, we still strive to
392 maintain accurate reference files across all supported platforms,
393 so it can be expected that all tests pass.
397 The actual outputs of the regression tests are in files in the
398 <filename>src/test/regress/results</filename> directory. The test
399 script uses <command>diff</command> to compare each output
400 file against the reference outputs stored in the
401 <filename>src/test/regress/expected</filename> directory. Any
402 differences are saved for your inspection in
403 <filename>src/test/regress/regression.diffs</filename>.
404 (When running a test suite other than the core tests, these files
405 of course appear in the relevant subdirectory,
406 not <filename>src/test/regress</filename>.)
411 like the <command>diff</command> options that are used by default, set the
412 environment variable <envar>PG_REGRESS_DIFF_OPTS</envar>, for
413 instance <literal>PG_REGRESS_DIFF_OPTS='-c'</literal>. (Or you
414 can run <command>diff</command> yourself, if you prefer.)
418 If for some reason a particular platform generates a <quote>failure</quote>
419 for a given test, but inspection of the output convinces you that
420 the result is valid, you can add a new comparison file to silence
421 the failure report in future test runs. See
422 <xref linkend="regress-variant"/> for details.
426 <title>Error Message Differences</title>
429 Some of the regression tests involve intentional invalid input
430 values. Error messages can come from either the
431 <productname>PostgreSQL</productname> code or from the host
432 platform system routines. In the latter case, the messages can
433 vary between platforms, but should reflect similar
434 information. These differences in messages will result in a
435 <quote>failed</quote> regression test that can be validated by
441 <title>Locale Differences</title>
444 If you run the tests against a server that was
445 initialized with a collation-order locale other than C, then
446 there might be differences due to sort order and subsequent
447 failures. The regression test suite is set up to handle this
448 problem by providing alternate result files that together are
449 known to handle a large number of locales.
453 To run the tests in a different locale when using the
454 temporary-installation method, pass the appropriate
455 locale-related environment variables on
456 the <command>make</command> command line, for example:
458 make check LANG=de_DE.utf8
460 (The regression test driver unsets <envar>LC_ALL</envar>, so it
461 does not work to choose the locale using that variable.) To use
462 no locale, either unset all locale-related environment variables
463 (or set them to <literal>C</literal>) or use the following
466 make check NO_LOCALE=1
468 When running the tests against an existing installation, the
469 locale setup is determined by the existing installation. To
470 change it, initialize the database cluster with a different
471 locale by passing the appropriate options
472 to <command>initdb</command>.
476 In general, it is advisable to try to run the
477 regression tests in the locale setup that is wanted for
478 production use, as this will exercise the locale- and
479 encoding-related code portions that will actually be used in
480 production. Depending on the operating system environment, you
481 might get failures, but then you will at least know what
482 locale-specific behaviors to expect when running real
488 <title>Date and Time Differences</title>
491 Most of the date and time results are dependent on the time zone
492 environment. The reference files are generated for time zone
493 <literal>PST8PDT</literal> (Berkeley, California), and there will be
494 apparent failures if the tests are not run with that time zone setting.
495 The regression test driver sets environment variable
496 <envar>PGTZ</envar> to <literal>PST8PDT</literal>, which normally
497 ensures proper results.
502 <title>Floating-Point Differences</title>
505 Some of the tests involve computing 64-bit floating-point numbers (<type>double
506 precision</type>) from table columns. Differences in
507 results involving mathematical functions of <type>double
508 precision</type> columns have been observed. The <literal>float8</literal> and
509 <literal>geometry</literal> tests are particularly prone to small differences
510 across platforms, or even with different compiler optimization settings.
511 Human eyeball comparison is needed to determine the real
512 significance of these differences which are usually 10 places to
513 the right of the decimal point.
517 Some systems display minus zero as <literal>-0</literal>, while others
518 just show <literal>0</literal>.
522 Some systems signal errors from <function>pow()</function> and
523 <function>exp()</function> differently from the mechanism
524 expected by the current <productname>PostgreSQL</productname>
530 <title>Row Ordering Differences</title>
533 You might see differences in which the same rows are output in a
534 different order than what appears in the expected file. In most cases
535 this is not, strictly speaking, a bug. Most of the regression test
536 scripts are not so pedantic as to use an <literal>ORDER BY</literal> for every single
537 <literal>SELECT</literal>, and so their result row orderings are not well-defined
538 according to the SQL specification. In practice, since we are
539 looking at the same queries being executed on the same data by the same
540 software, we usually get the same result ordering on all platforms,
541 so the lack of <literal>ORDER BY</literal> is not a problem. Some queries do exhibit
542 cross-platform ordering differences, however. When testing against an
543 already-installed server, ordering differences can also be caused by
544 non-C locale settings or non-default parameter settings, such as custom values
545 of <varname>work_mem</varname> or the planner cost parameters.
549 Therefore, if you see an ordering difference, it's not something to
550 worry about, unless the query does have an <literal>ORDER BY</literal> that your
551 result is violating. However, please report it anyway, so that we can add an
552 <literal>ORDER BY</literal> to that particular query to eliminate the bogus
553 <quote>failure</quote> in future releases.
557 You might wonder why we don't order all the regression test queries explicitly
558 to get rid of this issue once and for all. The reason is that that would
559 make the regression tests less useful, not more, since they'd tend
560 to exercise query plan types that produce ordered results to the
561 exclusion of those that don't.
566 <title>Insufficient Stack Depth</title>
569 If the <literal>errors</literal> test results in a server crash
570 at the <literal>select infinite_recurse()</literal> command, it means that
571 the platform's limit on process stack size is smaller than the
572 <xref linkend="guc-max-stack-depth"/> parameter indicates. This
573 can be fixed by running the server under a higher stack
574 size limit (4MB is recommended with the default value of
575 <varname>max_stack_depth</varname>). If you are unable to do that, an
576 alternative is to reduce the value of <varname>max_stack_depth</varname>.
580 On platforms supporting <function>getrlimit()</function>, the server should
581 automatically choose a safe value of <varname>max_stack_depth</varname>;
582 so unless you've manually overridden this setting, a failure of this
583 kind is a reportable bug.
588 <title>The <quote>random</quote> Test</title>
591 The <literal>random</literal> test script is intended to produce
592 random results. In very rare cases, this causes that regression
593 test to fail. Typing:
595 diff results/random.out expected/random.out
597 should produce only one or a few lines of differences. You need
598 not worry unless the random test fails repeatedly.
603 <title>Configuration Parameters</title>
606 When running the tests against an existing installation, some non-default
607 parameter settings could cause the tests to fail. For example, changing
608 parameters such as <varname>enable_seqscan</varname> or
609 <varname>enable_indexscan</varname> could cause plan changes that would
610 affect the results of tests that use <command>EXPLAIN</command>.
615 <!-- We might want to move the following section into the developer's guide. -->
616 <sect1 id="regress-variant">
617 <title>Variant Comparison Files</title>
620 Since some of the tests inherently produce environment-dependent
621 results, we have provided ways to specify alternate <quote>expected</quote>
622 result files. Each regression test can have several comparison files
623 showing possible results on different platforms. There are two
624 independent mechanisms for determining which comparison file is used
629 The first mechanism allows comparison files to be selected for
630 specific platforms. There is a mapping file,
631 <filename>src/test/regress/resultmap</filename>, that defines
632 which comparison file to use for each platform.
633 To eliminate bogus test <quote>failures</quote> for a particular platform,
634 you first choose or make a variant result file, and then add a line to the
635 <filename>resultmap</filename> file.
639 Each line in the mapping file is of the form
641 testname:output:platformpattern=comparisonfilename
643 The test name is just the name of the particular regression test
644 module. The output value indicates which output file to check. For the
645 standard regression tests, this is always <literal>out</literal>. The
646 value corresponds to the file extension of the output file.
647 The platform pattern is a pattern in the style of the Unix
648 tool <command>expr</command> (that is, a regular expression with an implicit
649 <literal>^</literal> anchor at the start). It is matched against the
650 platform name as printed by <command>config.guess</command>.
651 The comparison file name is the base name of the substitute result
656 For example: some systems lack a working <literal>strtof</literal> function,
657 for which our workaround causes rounding errors in the
658 <filename>float4</filename> regression test.
659 Therefore, we provide a variant comparison file,
660 <filename>float4-misrounded-input.out</filename>, which includes
661 the results to be expected on these systems. To silence the bogus
662 <quote>failure</quote> message on <systemitem>HP-UX 10</systemitem>
663 platforms, <filename>resultmap</filename> includes:
665 float4:out:hppa.*-hp-hpux10.*=float4-misrounded-input.out
667 which will trigger on any machine where the output of
668 <command>config.guess</command> matches <literal>hppa.*-hp-hpux10.*</literal>.
669 Other lines in <filename>resultmap</filename> select the variant comparison
670 file for other platforms where it's appropriate.
674 The second selection mechanism for variant comparison files is
675 much more automatic: it simply uses the <quote>best match</quote> among
676 several supplied comparison files. The regression test driver
677 script considers both the standard comparison file for a test,
678 <literal><replaceable>testname</replaceable>.out</literal>, and variant files named
679 <literal><replaceable>testname</replaceable>_<replaceable>digit</replaceable>.out</literal>
680 (where the <replaceable>digit</replaceable> is any single digit
681 <literal>0</literal>-<literal>9</literal>). If any such file is an exact match,
682 the test is considered to pass; otherwise, the one that generates
683 the shortest diff is used to create the failure report. (If
684 <filename>resultmap</filename> includes an entry for the particular
685 test, then the base <replaceable>testname</replaceable> is the substitute
686 name given in <filename>resultmap</filename>.)
690 For example, for the <literal>char</literal> test, the comparison file
691 <filename>char.out</filename> contains results that are expected
692 in the <literal>C</literal> and <literal>POSIX</literal> locales, while
693 the file <filename>char_1.out</filename> contains results sorted as
694 they appear in many other locales.
698 The best-match mechanism was devised to cope with locale-dependent
699 results, but it can be used in any situation where the test results
700 cannot be predicted easily from the platform name alone. A limitation of
701 this mechanism is that the test driver cannot tell which variant is
702 actually <quote>correct</quote> for the current environment; it will just pick
703 the variant that seems to work best. Therefore it is safest to use this
704 mechanism only for variant results that you are willing to consider
705 equally valid in all contexts.
710 <sect1 id="regress-tap">
711 <title>TAP Tests</title>
714 Various tests, particularly the client program tests
715 under <filename>src/bin</filename>, use the Perl TAP tools and are run
716 using the Perl testing program <command>prove</command>. You can pass
717 command-line options to <command>prove</command> by setting
718 the <command>make</command> variable <varname>PROVE_FLAGS</varname>, for example:
720 make -C src/bin check PROVE_FLAGS='--timer'
722 See the manual page of <command>prove</command> for more information.
726 The <command>make</command> variable <varname>PROVE_TESTS</varname>
727 can be used to define a whitespace-separated list of paths relative
728 to the <filename>Makefile</filename> invoking <command>prove</command>
729 to run the specified subset of tests instead of the default
730 <filename>t/*.pl</filename>. For example:
732 make check PROVE_TESTS='t/001_test1.pl t/003_test3.pl'
737 The TAP tests require the Perl module <literal>IPC::Run</literal>.
738 This module is available from CPAN or an operating system package.
742 <sect1 id="regress-coverage">
743 <title>Test Coverage Examination</title>
746 The PostgreSQL source code can be compiled with coverage testing
747 instrumentation, so that it becomes possible to examine which
748 parts of the code are covered by the regression tests or any other
749 test suite that is run with the code. This is currently supported
750 when compiling with GCC and requires the <command>gcov</command>
751 and <command>lcov</command> programs.
755 A typical workflow would look like this:
757 ./configure --enable-coverage ... OTHER OPTIONS ...
759 make check # or other test suite
762 Then point your HTML browser
763 to <filename>coverage/index.html</filename>.
764 The <command>make</command> commands also work in subdirectories.
768 If you don't have <command>lcov</command> or prefer text output over an
769 HTML report, you can also run
773 instead of <literal>make coverage-html</literal>, which will
774 produce <filename>.gcov</filename> output files for each source file
775 relevant to the test. (<literal>make coverage</literal> and <literal>make
776 coverage-html</literal> will overwrite each other's files, so mixing them
781 To reset the execution counts between test runs, run: