src/test/isolation/README

Isolation tests
===============

This directory contains a set of tests for concurrent behaviors in
PostgreSQL.  These tests require running multiple interacting transactions,
which requires management of multiple concurrent connections, and therefore
can't be tested using the normal pg_regress program.  The name "isolation"
comes from the fact that the original motivation was to test the
serializable isolation level; but tests for other sorts of concurrent
behaviors have been added as well.

You can run the tests against the current build tree by typing
    make check
Alternatively, you can run against an existing installation by typing
    make installcheck
(This will contact a server at the default port expected by libpq.
You can set PGPORT and so forth in your environment to control this.)

To run just specific test(s) against an installed server,
you can do something like
    ./pg_isolation_regress fk-contention fk-deadlock
(look into the specs/ subdirectory to see the available tests).

Certain tests require the server's max_prepared_transactions parameter to be
set to at least 3; therefore they are not run by default.  To include them in
the test run, use
    make check-prepared-txns
or
    make installcheck-prepared-txns
after making sure the server configuration is correct (see TEMP_CONFIG
to adjust this in the "check" case).

To define tests with overlapping transactions, we use test specification
files with a custom syntax, which is described in the next section.  To add
a new test, place a spec file in the specs/ subdirectory, add the expected
output in the expected/ subdirectory, and add the test's name to the
isolation_schedule file.

isolationtester is a program that uses libpq to open multiple connections,
and executes a test specified by a spec file. A libpq connection string
specifies the server and database to connect to; defaults derived from
environment variables are used otherwise.

pg_isolation_regress is a tool similar to pg_regress, but instead of using
psql to execute a test, it uses isolationtester.  It accepts all the same
command-line arguments as pg_regress.

By default, isolationtester will wait at most 300 seconds (5 minutes)
for any one test step to complete.  If you need to adjust this, set
the environment variable PGISOLATIONTIMEOUT to the desired timeout
in seconds.


Test specification
==================

Each isolation test is defined by a specification file, stored in the specs
subdirectory.  A test specification defines some SQL "steps", groups them
into "sessions" (where all the steps of one session will be run in the
same backend), and specifies the "permutations" or orderings of the steps
that are to be run.

A test specification consists of four parts, in this order:

setup { <SQL> }

  The given SQL block is executed once (per permutation) before running
  the test.  Create any test tables or other required objects here.  This
  part is optional.  Multiple setup blocks are allowed if needed; each is
  run separately, in the given order.  (The reason for allowing multiple
  setup blocks is that each block is run as a single PQexec submission,
  and some statements such as VACUUM cannot be combined with others in such
  a block.)

teardown { <SQL> }

  The teardown SQL block is executed once after the test is finished. Use
  this to clean up in preparation for the next permutation, e.g dropping
  any test tables created by setup. This part is optional.

session <name>

  There are normally several "session" parts in a spec file. Each
  session is executed in its own connection. A session part consists
  of three parts: setup, teardown and one or more "steps". The per-session
  setup and teardown parts have the same syntax as the per-test setup and
  teardown described above, but they are executed in each session. The setup
  part might, for example, contain a "BEGIN" command to begin a transaction.

  Each step has the syntax

  step <name> { <SQL> }

  where <name> is a name identifying this step, and <SQL> is a SQL statement
  (or statements, separated by semicolons) that is executed in the step.
  Step names must be unique across the whole spec file.

permutation <step name> ...

  A permutation line specifies a list of steps that are run in that order.
  Any number of permutation lines can appear.  If no permutation lines are
  given, the test program automatically runs all possible interleavings
  of the steps from each session (running the steps of any one session in
  order).  Note that the list of steps in a manually specified
  "permutation" line doesn't actually have to be a permutation of the
  available steps; it could for instance repeat some steps more than once,
  or leave others out.  Also, each step name can be annotated with some
  parenthesized markers, which are described below.

Session and step names are SQL identifiers, either plain or double-quoted.
A difference from standard SQL is that no case-folding occurs, so that
FOO and "FOO" are the same name while FOO and Foo are different,
whether you quote them or not.  You must use quotes if you want to use
an isolation test keyword (such as "permutation") as a name.

A # character begins a comment, which extends to the end of the line.
(This does not work inside <SQL> blocks, however.  Use the usual SQL
comment conventions there.)

There is no way to include a "}" character in an <SQL> block.

For each permutation of the session steps (whether these are manually
specified in the spec file, or automatically generated), the isolation
tester runs the main setup part, then per-session setup parts, then
the selected session steps, then per-session teardown, then the main
teardown script.  Each selected step is sent to the connection associated
with its session.  The main setup and teardown scripts are run in a
separate "control" session.


Support for blocking commands
=============================

Each step may contain commands that block until further action has been taken
(most likely, some other session runs a step that unblocks it or causes a
deadlock).  A test that uses this ability must manually specify valid
permutations, i.e. those that would not expect a blocked session to execute a
command.  If a test fails to follow that rule, isolationtester will cancel it
after PGISOLATIONTIMEOUT seconds.  If the cancel doesn't work, isolationtester
will exit uncleanly after a total of twice PGISOLATIONTIMEOUT.  Testing
invalid permutations should be avoided because they can make the isolation
tests take a very long time to run, and they serve no useful testing purpose.

Note that isolationtester recognizes that a command has blocked by looking
to see if it is shown as waiting in the pg_locks view; therefore, only
blocks on heavyweight locks will be detected.


Dealing with race conditions
============================

In some cases, the isolationtester's output for a test script may vary
due to timing issues.  One way to deal with that is to create variant
expected-files, which follow the usual PG convention that variants for
foo.spec are named foo_1.out, foo_2.out, etc.  However, this method is
discouraged since the extra files are a nuisance for maintenance.
Instead, it's usually possible to stabilize the test output by applying
special markers to some of the step names listed in a permutation line.

The general form of a permutation entry is

	<step name> [ ( <marker> [ , <marker> ... ] ) ]

where each marker defines a "blocking condition".  The step will not be
reported as completed before all the blocking conditions are satisfied.
The possible markers are:

	*
	<other step name>
	<other step name> notices <n>

An asterisk marker, such as mystep(*), forces the isolationtester to
report the step as "waiting" as soon as it's been launched, regardless of
whether it would have been detected as waiting later.  This is useful for
stabilizing cases that are sometimes reported as waiting and other times
reported as immediately completing, depending on the relative speeds of
the step and the isolationtester's status-monitoring queries.

A marker consisting solely of a step name indicates that this step may
not be reported as completing until that other step has completed.
This allows stabilizing cases where two queries might be seen to complete
in either order.  Note that this step can be *launched* before the other
step has completed.  (If the other step is used more than once in the
current permutation, this step cannot complete while any of those
instances is active.)

A marker of the form "<other step name> notices <n>" (where <n> is a
positive integer) indicates that this step may not be reported as
completing until the other step's session has returned at least <n>
NOTICE messages, counting from when this step is launched.  This is useful
for stabilizing cases where a step can return NOTICE messages before it
actually completes, and those messages must be synchronized with the
completions of other steps.

Notice that these markers can only delay reporting of the completion
of a step, not the launch of a step.  The isolationtester will launch
the next step in a permutation as soon as (A) all prior steps of the
same session are done, and (B) the immediately preceding step in the
permutation is done or deemed blocked.  For this purpose, "deemed
blocked" means that it has been seen to be waiting on a database lock,
or that it is complete but the report of its completion is delayed by
one of these markers.

In some cases it is important not to launch a step until after the
completion of a step in another session that could have been deemed
blocked.  An example is that if step s1 in session A is issuing a
cancel for step s2 in session B, we'd better not launch B's next step
till we're sure s1 is done.  If s1 is blockable, trouble could ensue.
The best way to prevent that is to create an empty step in session A
and run it, without any markers, just before the next session B step.
The empty step cannot be launched until s1 is done, and in turn the
next session B step cannot be launched until the empty step finishes.