1src/test/isolation/README 2 3Isolation tests 4=============== 5 6This directory contains a set of tests for concurrent behaviors in 7PostgreSQL. These tests require running multiple interacting transactions, 8which requires management of multiple concurrent connections, and therefore 9can't be tested using the normal pg_regress program. The name "isolation" 10comes from the fact that the original motivation was to test the 11serializable isolation level; but tests for other sorts of concurrent 12behaviors have been added as well. 13 14You can run the tests against the current build tree by typing 15 make check 16Alternatively, you can run against an existing installation by typing 17 make installcheck 18(This will contact a server at the default port expected by libpq. 19You can set PGPORT and so forth in your environment to control this.) 20 21To run just specific test(s) against an installed server, 22you can do something like 23 ./pg_isolation_regress fk-contention fk-deadlock 24(look into the specs/ subdirectory to see the available tests). 25 26Certain tests require the server's max_prepared_transactions parameter to be 27set to at least 3; therefore they are not run by default. To include them in 28the test run, use 29 make check-prepared-txns 30or 31 make installcheck-prepared-txns 32after making sure the server configuration is correct (see TEMP_CONFIG 33to adjust this in the "check" case). 34 35To define tests with overlapping transactions, we use test specification 36files with a custom syntax, which is described in the next section. To add 37a new test, place a spec file in the specs/ subdirectory, add the expected 38output in the expected/ subdirectory, and add the test's name to the 39isolation_schedule file. 40 41isolationtester is a program that uses libpq to open multiple connections, 42and executes a test specified by a spec file. A libpq connection string 43specifies the server and database to connect to; defaults derived from 44environment variables are used otherwise. 45 46pg_isolation_regress is a tool similar to pg_regress, but instead of using 47psql to execute a test, it uses isolationtester. It accepts all the same 48command-line arguments as pg_regress. 49 50By default, isolationtester will wait at most 300 seconds (5 minutes) 51for any one test step to complete. If you need to adjust this, set 52the environment variable PGISOLATIONTIMEOUT to the desired timeout 53in seconds. 54 55 56Test specification 57================== 58 59Each isolation test is defined by a specification file, stored in the specs 60subdirectory. A test specification defines some SQL "steps", groups them 61into "sessions" (where all the steps of one session will be run in the 62same backend), and specifies the "permutations" or orderings of the steps 63that are to be run. 64 65A test specification consists of four parts, in this order: 66 67setup { <SQL> } 68 69 The given SQL block is executed once (per permutation) before running 70 the test. Create any test tables or other required objects here. This 71 part is optional. Multiple setup blocks are allowed if needed; each is 72 run separately, in the given order. (The reason for allowing multiple 73 setup blocks is that each block is run as a single PQexec submission, 74 and some statements such as VACUUM cannot be combined with others in such 75 a block.) 76 77teardown { <SQL> } 78 79 The teardown SQL block is executed once after the test is finished. Use 80 this to clean up in preparation for the next permutation, e.g dropping 81 any test tables created by setup. This part is optional. 82 83session <name> 84 85 There are normally several "session" parts in a spec file. Each 86 session is executed in its own connection. A session part consists 87 of three parts: setup, teardown and one or more "steps". The per-session 88 setup and teardown parts have the same syntax as the per-test setup and 89 teardown described above, but they are executed in each session. The setup 90 part might, for example, contain a "BEGIN" command to begin a transaction. 91 92 Each step has the syntax 93 94 step <name> { <SQL> } 95 96 where <name> is a name identifying this step, and <SQL> is a SQL statement 97 (or statements, separated by semicolons) that is executed in the step. 98 Step names must be unique across the whole spec file. 99 100permutation <step name> ... 101 102 A permutation line specifies a list of steps that are run in that order. 103 Any number of permutation lines can appear. If no permutation lines are 104 given, the test program automatically runs all possible interleavings 105 of the steps from each session (running the steps of any one session in 106 order). Note that the list of steps in a manually specified 107 "permutation" line doesn't actually have to be a permutation of the 108 available steps; it could for instance repeat some steps more than once, 109 or leave others out. Also, each step name can be annotated with some 110 parenthesized markers, which are described below. 111 112Session and step names are SQL identifiers, either plain or double-quoted. 113A difference from standard SQL is that no case-folding occurs, so that 114FOO and "FOO" are the same name while FOO and Foo are different, 115whether you quote them or not. You must use quotes if you want to use 116an isolation test keyword (such as "permutation") as a name. 117 118A # character begins a comment, which extends to the end of the line. 119(This does not work inside <SQL> blocks, however. Use the usual SQL 120comment conventions there.) 121 122There is no way to include a "}" character in an <SQL> block. 123 124For each permutation of the session steps (whether these are manually 125specified in the spec file, or automatically generated), the isolation 126tester runs the main setup part, then per-session setup parts, then 127the selected session steps, then per-session teardown, then the main 128teardown script. Each selected step is sent to the connection associated 129with its session. The main setup and teardown scripts are run in a 130separate "control" session. 131 132 133Support for blocking commands 134============================= 135 136Each step may contain commands that block until further action has been taken 137(most likely, some other session runs a step that unblocks it or causes a 138deadlock). A test that uses this ability must manually specify valid 139permutations, i.e. those that would not expect a blocked session to execute a 140command. If a test fails to follow that rule, isolationtester will cancel it 141after PGISOLATIONTIMEOUT seconds. If the cancel doesn't work, isolationtester 142will exit uncleanly after a total of twice PGISOLATIONTIMEOUT. Testing 143invalid permutations should be avoided because they can make the isolation 144tests take a very long time to run, and they serve no useful testing purpose. 145 146Note that isolationtester recognizes that a command has blocked by looking 147to see if it is shown as waiting in the pg_locks view; therefore, only 148blocks on heavyweight locks will be detected. 149 150 151Dealing with race conditions 152============================ 153 154In some cases, the isolationtester's output for a test script may vary 155due to timing issues. One way to deal with that is to create variant 156expected-files, which follow the usual PG convention that variants for 157foo.spec are named foo_1.out, foo_2.out, etc. However, this method is 158discouraged since the extra files are a nuisance for maintenance. 159Instead, it's usually possible to stabilize the test output by applying 160special markers to some of the step names listed in a permutation line. 161 162The general form of a permutation entry is 163 164 <step name> [ ( <marker> [ , <marker> ... ] ) ] 165 166where each marker defines a "blocking condition". The step will not be 167reported as completed before all the blocking conditions are satisfied. 168The possible markers are: 169 170 * 171 <other step name> 172 <other step name> notices <n> 173 174An asterisk marker, such as mystep(*), forces the isolationtester to 175report the step as "waiting" as soon as it's been launched, regardless of 176whether it would have been detected as waiting later. This is useful for 177stabilizing cases that are sometimes reported as waiting and other times 178reported as immediately completing, depending on the relative speeds of 179the step and the isolationtester's status-monitoring queries. 180 181A marker consisting solely of a step name indicates that this step may 182not be reported as completing until that other step has completed. 183This allows stabilizing cases where two queries might be seen to complete 184in either order. Note that this step can be *launched* before the other 185step has completed. (If the other step is used more than once in the 186current permutation, this step cannot complete while any of those 187instances is active.) 188 189A marker of the form "<other step name> notices <n>" (where <n> is a 190positive integer) indicates that this step may not be reported as 191completing until the other step's session has returned at least <n> 192NOTICE messages, counting from when this step is launched. This is useful 193for stabilizing cases where a step can return NOTICE messages before it 194actually completes, and those messages must be synchronized with the 195completions of other steps. 196 197Notice that these markers can only delay reporting of the completion 198of a step, not the launch of a step. The isolationtester will launch 199the next step in a permutation as soon as (A) all prior steps of the 200same session are done, and (B) the immediately preceding step in the 201permutation is done or deemed blocked. For this purpose, "deemed 202blocked" means that it has been seen to be waiting on a database lock, 203or that it is complete but the report of its completion is delayed by 204one of these markers. 205 206In some cases it is important not to launch a step until after the 207completion of a step in another session that could have been deemed 208blocked. An example is that if step s1 in session A is issuing a 209cancel for step s2 in session B, we'd better not launch B's next step 210till we're sure s1 is done. If s1 is blockable, trouble could ensue. 211The best way to prevent that is to create an empty step in session A 212and run it, without any markers, just before the next session B step. 213The empty step cannot be launched until s1 is done, and in turn the 214next session B step cannot be launched until the empty step finishes. 215