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 { } 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 { } 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 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 { } where is a name identifying this step, and 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 ... 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 blocks, however. Use the usual SQL comment conventions there.) There is no way to include a "}" character in an 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 [ ( [ , ... ] ) ] 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: * notices 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 " notices " (where is a positive integer) indicates that this step may not be reported as completing until the other step's session has returned at least 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.