On Fri, Feb 11, 2011 at 02:13:22AM -0500, Noah Misch wrote:
> Automated tests would go a long way toward building confidence that this patch
> does the right thing. Thanks to the SSI patch, we now have an in-tree test
> framework for testing interleaved transactions. The only thing it needs to be
> suitable for this work is a way to handle blocked commands. If you like, I
> can
> try to whip something up for that.
[off-list ACK followed]
Here's a patch implementing that. It applies to master, with or without your
KEY LOCK patch also applied, though the expected outputs reflect the
improvements from your patch. I add three isolation test specs:
fk-contention: blocking-only test case from your blog post
fk-deadlock: the deadlocking test case I used during patch review
fk-deadlock2: Joel Jacobson's deadlocking test case
When a spec permutation would have us run a command in a currently-blocked
session, we cannot implement that permutation. Such permutations represent
impossible real-world scenarios, anyway. For now, I just explicitly name the
valid permutations in each spec file. If the test harness detects this problem,
we abort the current test spec. It might be nicer to instead cancel all
outstanding queries, issue rollbacks in all sessions, and continue with other
permutations. I hesitated to do that, because we currently leave all
transaction control in the hands of the test spec.
I only support one waiting command at a time. As long as one commands continues
to wait, I run other commands to completion synchronously. This decision has no
impact on the current test specs, which all have two sessions. It avoided a
touchy policy decision concerning deadlock detection. If two commands have
blocked, it may be that a third command needs to run before they will unblock,
or it may be that the two commands have formed a deadlock. We won't know for
sure until deadlock_timeout elapses. If it's possible to run the next step in
the permutation (i.e., it uses a different session from any blocked command), we
can either do so immediately or wait out the deadlock_timeout first. The latter
slows the test suite, but it makes the output more natural -- more like what one
would typically after running the commands by hand. If anyone can think of a
sound general policy, that would be helpful. For now, I've punted.
With a default postgresql.conf, deadlock_timeout constitutes most of the run
time. Reduce it to 20ms to accelerate things when running the tests repeatedly.
Since timing dictates which query participating in a deadlock will be chosen for
cancellation, the expected outputs bearing deadlock errors are unstable. I'm
not sure how much it will come up in practice, so I have not included expected
output variations to address this.
I think this will work on Windows as well as pgbench does, but I haven't
verified that.
Sorry for the delay on this.
nm
*** /dev/null
--- b/src/test/isolation/expected/fk-contention.out
***************
*** 0 ****
--- 1,16 ----
+ Parsed test spec with 2 sessions
+
+ starting permutation: ins com upd
+ step ins: INSERT INTO bar VALUES (42);
+ step com: COMMIT;
+ step upd: UPDATE foo SET b = 'Hello World';
+
+ starting permutation: ins upd com
+ step ins: INSERT INTO bar VALUES (42);
+ step upd: UPDATE foo SET b = 'Hello World';
+ step com: COMMIT;
+
+ starting permutation: upd ins com
+ step upd: UPDATE foo SET b = 'Hello World';
+ step ins: INSERT INTO bar VALUES (42);
+ step com: COMMIT;
*** /dev/null
--- b/src/test/isolation/expected/fk-deadlock.out
***************
*** 0 ****
--- 1,63 ----
+ Parsed test spec with 2 sessions
+
+ starting permutation: s1i s1u s1c s2i s2u s2c
+ step s1i: INSERT INTO child VALUES (1, 1);
+ step s1u: UPDATE parent SET aux = 'bar';
+ step s1c: COMMIT;
+ step s2i: INSERT INTO child VALUES (2, 1);
+ step s2u: UPDATE parent SET aux = 'baz';
+ step s2c: COMMIT;
+
+ starting permutation: s1i s1u s2i s1c s2u s2c
+ step s1i: INSERT INTO child VALUES (1, 1);
+ step s1u: UPDATE parent SET aux = 'bar';
+ step s2i: INSERT INTO child VALUES (2, 1); <waiting ...>
+ step s1c: COMMIT;
+ step s2i: <... completed>
+ step s2u: UPDATE parent SET aux = 'baz';
+ step s2c: COMMIT;
+
+ starting permutation: s1i s2i s1u s2u s1c s2c
+ step s1i: INSERT INTO child VALUES (1, 1);
+ step s2i: INSERT INTO child VALUES (2, 1);
+ step s1u: UPDATE parent SET aux = 'bar';
+ step s2u: UPDATE parent SET aux = 'baz'; <waiting ...>
+ step s1c: COMMIT;
+ step s2u: <... completed>
+ step s2c: COMMIT;
+
+ starting permutation: s1i s2i s2u s1u s2c s1c
+ step s1i: INSERT INTO child VALUES (1, 1);
+ step s2i: INSERT INTO child VALUES (2, 1);
+ step s2u: UPDATE parent SET aux = 'baz';
+ step s1u: UPDATE parent SET aux = 'bar'; <waiting ...>
+ step s2c: COMMIT;
+ step s1u: <... completed>
+ step s1c: COMMIT;
+
+ starting permutation: s2i s1i s1u s2u s1c s2c
+ step s2i: INSERT INTO child VALUES (2, 1);
+ step s1i: INSERT INTO child VALUES (1, 1);
+ step s1u: UPDATE parent SET aux = 'bar';
+ step s2u: UPDATE parent SET aux = 'baz'; <waiting ...>
+ step s1c: COMMIT;
+ step s2u: <... completed>
+ step s2c: COMMIT;
+
+ starting permutation: s2i s1i s2u s1u s2c s1c
+ step s2i: INSERT INTO child VALUES (2, 1);
+ step s1i: INSERT INTO child VALUES (1, 1);
+ step s2u: UPDATE parent SET aux = 'baz';
+ step s1u: UPDATE parent SET aux = 'bar'; <waiting ...>
+ step s2c: COMMIT;
+ step s1u: <... completed>
+ step s1c: COMMIT;
+
+ starting permutation: s2i s2u s1i s2c s1u s1c
+ step s2i: INSERT INTO child VALUES (2, 1);
+ step s2u: UPDATE parent SET aux = 'baz';
+ step s1i: INSERT INTO child VALUES (1, 1); <waiting ...>
+ step s2c: COMMIT;
+ step s1i: <... completed>
+ step s1u: UPDATE parent SET aux = 'bar';
+ step s1c: COMMIT;
*** /dev/null
--- b/src/test/isolation/expected/fk-deadlock2.out
***************
*** 0 ****
--- 1,106 ----
+ Parsed test spec with 2 sessions
+
+ starting permutation: s1u1 s1u2 s1c s2u1 s2u2 s2c
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1c: COMMIT;
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2c: COMMIT;
+
+ starting permutation: s1u1 s1u2 s2u1 s1c s2u2 s2c
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s1c: COMMIT;
+ step s2u1: <... completed>
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2c: COMMIT;
+
+ starting permutation: s1u1 s2u1 s1u2 s2u2 s1c s2c
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u2: <... completed>
+ ERROR: deadlock detected
+ step s1c: COMMIT;
+ step s2c: COMMIT;
+
+ starting permutation: s1u1 s2u1 s1u2 s2u2 s2c s1c
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u2: <... completed>
+ ERROR: deadlock detected
+ step s2c: COMMIT;
+ step s1c: COMMIT;
+
+ starting permutation: s1u1 s2u1 s2u2 s1u2 s1c s2c
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: <... completed>
+ ERROR: deadlock detected
+ step s1c: COMMIT;
+ step s2c: COMMIT;
+
+ starting permutation: s1u1 s2u1 s2u2 s1u2 s2c s1c
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: <... completed>
+ ERROR: deadlock detected
+ step s2c: COMMIT;
+ step s1c: COMMIT;
+
+ starting permutation: s2u1 s1u1 s1u2 s2u2 s1c s2c
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u2: <... completed>
+ ERROR: deadlock detected
+ step s1c: COMMIT;
+ step s2c: COMMIT;
+
+ starting permutation: s2u1 s1u1 s1u2 s2u2 s2c s1c
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u2: <... completed>
+ ERROR: deadlock detected
+ step s2c: COMMIT;
+ step s1c: COMMIT;
+
+ starting permutation: s2u1 s1u1 s2u2 s1u2 s1c s2c
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: <... completed>
+ ERROR: deadlock detected
+ step s1c: COMMIT;
+ step s2c: COMMIT;
+
+ starting permutation: s2u1 s1u1 s2u2 s1u2 s2c s1c
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; <waiting ...>
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: <... completed>
+ ERROR: deadlock detected
+ step s2c: COMMIT;
+ step s1c: COMMIT;
+
+ starting permutation: s2u1 s2u2 s1u1 s2c s1u2 s1c
+ step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1;
+ step s2c: COMMIT;
+ step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2;
+ step s1c: COMMIT;
*** a/src/test/isolation/isolation_schedule
--- b/src/test/isolation/isolation_schedule
***************
*** 9,11 **** test: ri-trigger
--- 9,14 ----
test: partial-index
test: two-ids
test: multiple-row-versions
+ test: fk-contention
+ test: fk-deadlock
+ test: fk-deadlock2
*** a/src/test/isolation/isolationtester.c
--- b/src/test/isolation/isolationtester.c
***************
*** 9,23 ****
#include <windows.h>
#endif
#include <stddef.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
- #include "libpq-fe.h"
#include "isolationtester.h"
static PGconn **conns = NULL;
static int nconns = 0;
static void run_all_permutations(TestSpec *testspec);
--- 9,36 ----
#include <windows.h>
#endif
+ #include <errno.h>
+ #include <unistd.h>
#include <stddef.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
+ #ifdef HAVE_SYS_SELECT_H
+ #include <sys/select.h>
+ #endif
+
+ #include "libpq-fe.h"
#include "isolationtester.h"
+ #define PREP_WAITING "isolationtester_waiting"
+
+ /*
+ * conns[0] is the global setup, teardown, and watchdog connection.
Additional
+ * connections represent spec-defined sessions.
+ */
static PGconn **conns = NULL;
+ static const char **backend_ids = NULL;
static int nconns = 0;
static void run_all_permutations(TestSpec *testspec);
***************
*** 25,30 **** static void run_all_permutations_recurse(TestSpec *testspec, int
nsteps, Step **
--- 38,47 ----
static void run_named_permutations(TestSpec *testspec);
static void run_permutation(TestSpec *testspec, int nsteps, Step **steps);
+ #define STEP_NONBLOCK 0x1 /* return 0 as soon as cmd waits for a lock */
+ #define STEP_RETRY 0x2 /* this is a retry of a
previously-waiting cmd */
+ static int try_complete_step(Step *step, int flags);
+
static int step_qsort_cmp(const void *a, const void *b);
static int step_bsearch_cmp(const void *a, const void *b);
***************
*** 46,51 **** main(int argc, char **argv)
--- 63,69 ----
const char *conninfo;
TestSpec *testspec;
int i;
+ PGresult *res;
/*
* If the user supplies a parameter on the command line, use it as the
***************
*** 63,75 **** main(int argc, char **argv)
testspec = &parseresult;
printf("Parsed test spec with %d sessions\n", testspec->nsessions);
! /* Establish connections to the database, one for each session */
! nconns = testspec->nsessions;
conns = calloc(nconns, sizeof(PGconn *));
! for (i = 0; i < testspec->nsessions; i++)
{
- PGresult *res;
-
conns[i] = PQconnectdb(conninfo);
if (PQstatus(conns[i]) != CONNECTION_OK)
{
--- 81,95 ----
testspec = &parseresult;
printf("Parsed test spec with %d sessions\n", testspec->nsessions);
! /*
! * Establish connections to the database, one for each session and an
extra
! * for lock wait detection and global work.
! */
! nconns = 1 + testspec->nsessions;
conns = calloc(nconns, sizeof(PGconn *));
! backend_ids = calloc(nconns, sizeof(*backend_ids));
! for (i = 0; i < nconns; i++)
{
conns[i] = PQconnectdb(conninfo);
if (PQstatus(conns[i]) != CONNECTION_OK)
{
***************
*** 89,94 **** main(int argc, char **argv)
--- 109,136 ----
exit_nicely();
}
PQclear(res);
+
+ /* Get the backend ID for lock wait checking. */
+ res = PQexec(conns[i], "SELECT i FROM
pg_stat_get_backend_idset() t(i) "
+ "WHERE pg_stat_get_backend_pid(i) =
pg_backend_pid()");
+ if (PQresultStatus(res) == PGRES_TUPLES_OK)
+ {
+ if (PQntuples(res) == 1 && PQnfields(res) == 1)
+ backend_ids[i] = strdup(PQgetvalue(res, 0, 0));
+ else
+ {
+ fprintf(stderr, "backend id query returned %d
rows and %d columns, expected 1 row and 1 column",
+ PQntuples(res), PQnfields(res));
+ exit_nicely();
+ }
+ }
+ else
+ {
+ fprintf(stderr, "backend id query failed: %s",
+ PQerrorMessage(conns[i]));
+ exit_nicely();
+ }
+ PQclear(res);
}
/* Set the session index fields in steps. */
***************
*** 100,105 **** main(int argc, char **argv)
--- 142,157 ----
session->steps[stepindex]->session = i;
}
+ res = PQprepare(conns[0], PREP_WAITING,
+ "SELECT 1 WHERE
pg_stat_get_backend_waiting($1)", 0, NULL);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "prepare of lock wait query failed: %s",
+ PQerrorMessage(conns[0]));
+ exit_nicely();
+ }
+ PQclear(res);
+
/*
* Run the permutations specified in the spec, or all if none were
* explicitly specified.
***************
*** 254,259 **** run_permutation(TestSpec *testspec, int nsteps, Step **steps)
--- 306,312 ----
{
PGresult *res;
int i;
+ Step *waiting = NULL;
printf("\nstarting permutation:");
for (i = 0; i < nsteps; i++)
***************
*** 277,288 **** run_permutation(TestSpec *testspec, int nsteps, Step **steps)
{
if (testspec->sessions[i]->setupsql)
{
! res = PQexec(conns[i], testspec->sessions[i]->setupsql);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "setup of session %s failed:
%s",
testspec->sessions[i]->name,
! PQerrorMessage(conns[0]));
exit_nicely();
}
PQclear(res);
--- 330,341 ----
{
if (testspec->sessions[i]->setupsql)
{
! res = PQexec(conns[i+1],
testspec->sessions[i]->setupsql);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "setup of session %s failed:
%s",
testspec->sessions[i]->name,
! PQerrorMessage(conns[i+1]));
exit_nicely();
}
PQclear(res);
***************
*** 293,334 **** run_permutation(TestSpec *testspec, int nsteps, Step **steps)
for (i = 0; i < nsteps; i++)
{
Step *step = steps[i];
- printf("step %s: %s\n", step->name, step->sql);
- res = PQexec(conns[step->session], step->sql);
! switch(PQresultStatus(res))
{
! case PGRES_COMMAND_OK:
! break;
!
! case PGRES_TUPLES_OK:
! printResultSet(res);
! break;
! case PGRES_FATAL_ERROR:
! /* Detail may contain xid values, so just show
primary. */
! printf("%s: %s\n", PQresultErrorField(res,
PG_DIAG_SEVERITY),
! PQresultErrorField(res,
PG_DIAG_MESSAGE_PRIMARY));
! break;
! default:
! printf("unexpected result status: %s\n",
! PQresStatus(PQresultStatus(res)));
}
! PQclear(res);
}
/* Perform per-session teardown */
for (i = 0; i < testspec->nsessions; i++)
{
if (testspec->sessions[i]->teardownsql)
{
! res = PQexec(conns[i],
testspec->sessions[i]->teardownsql);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "teardown of session %s failed:
%s",
testspec->sessions[i]->name,
! PQerrorMessage(conns[0]));
/* don't exit on teardown failure */
}
PQclear(res);
--- 346,387 ----
for (i = 0; i < nsteps; i++)
{
Step *step = steps[i];
! if (!PQsendQuery(conns[1 + step->session], step->sql))
{
! fprintf(stdout, "failed to send query: %s\n",
! PQerrorMessage(conns[1 +
step->session]));
! exit_nicely();
! }
! if (waiting != NULL)
! {
! /* Some other step is already waiting: just block. */
! try_complete_step(step, 0);
! /* See if this step unblocked the waiting step. */
! if (try_complete_step(waiting, STEP_NONBLOCK |
STEP_RETRY))
! waiting = NULL;
}
! else if (!try_complete_step(step, STEP_NONBLOCK))
! waiting = step;
}
+ /* Finish any waiting query. */
+ if (waiting != NULL)
+ try_complete_step(waiting, STEP_RETRY);
+
/* Perform per-session teardown */
for (i = 0; i < testspec->nsessions; i++)
{
if (testspec->sessions[i]->teardownsql)
{
! res = PQexec(conns[i+1],
testspec->sessions[i]->teardownsql);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "teardown of session %s failed:
%s",
testspec->sessions[i]->name,
! PQerrorMessage(conns[i+1]));
/* don't exit on teardown failure */
}
PQclear(res);
***************
*** 350,355 **** run_permutation(TestSpec *testspec, int nsteps, Step **steps)
--- 403,507 ----
}
}
+ /*
+ * Our caller already sent the query associated with this step. Wait for it
to
+ * either complete or (only when given the STEP_NONBLOCK flag) to block while
+ * waiting for a lock. We assume that any lock wait will persist until we
have
+ * executed additional steps in the permutation. This is not fully robust --
a
+ * concurrent autovacuum could briefly take a lock with which we conflict.
The
+ * risk may be low enough to discount.
+ *
+ * When calling this function on behalf of a given step for a second or later
+ * time, pass the STEP_RETRY flag. This only affects the messages printed.
+ *
+ * If the STEP_NONBLOCK flag was specified and the query is waiting to
acquire a
+ * lock, returns 0. Otherwise, returns 1.
+ */
+ static int
+ try_complete_step(Step *step, int flags)
+ {
+ PGconn *conn = conns[1 + step->session];
+ fd_set read_set;
+ struct timeval timeout;
+ int sock = PQsocket(conn);
+ int ret;
+ PGresult *res;
+
+ FD_ZERO(&read_set);
+
+ while (flags & STEP_NONBLOCK && PQisBusy(conn))
+ {
+ FD_SET(sock, &read_set);
+ timeout.tv_sec = 0;
+ timeout.tv_usec = 10000; /* Check for lock waits every
10ms. */
+
+ ret = select(sock + 1, &read_set, NULL, NULL, &timeout);
+ if (ret < 0) /* error in select() */
+ {
+ fprintf(stderr, "select failed: %s\n", strerror(errno));
+ exit_nicely();
+ }
+ else if (ret == 0) /* select() timeout: check for lock
wait */
+ {
+ int ntuples;
+
+ res = PQexecPrepared(conns[0], PREP_WAITING, 1,
+
&backend_ids[step->session + 1],
+ NULL, NULL, 0);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ fprintf(stderr, "lock wait query failed: %s",
+ PQerrorMessage(conn));
+ exit_nicely();
+ }
+ ntuples = PQntuples(res);
+ PQclear(res);
+
+ if (ntuples >= 1) /* waiting to acquire a lock */
+ {
+ if (!(flags & STEP_RETRY))
+ printf("step %s: %s <waiting ...>\n",
+ step->name, step->sql);
+ return 0;
+ }
+ /* else, not waiting: give it more time */
+ }
+ else if (!PQconsumeInput(conn)) /* select(): data available */
+ {
+ fprintf(stderr, "PQconsumeInput failed: %s",
PQerrorMessage(conn));
+ exit_nicely();
+ }
+ }
+
+ if (flags & STEP_RETRY)
+ printf("step %s: <... completed>\n", step->name);
+ else
+ printf("step %s: %s\n", step->name, step->sql);
+
+ while ((res = PQgetResult(conn)))
+ {
+ switch (PQresultStatus(res))
+ {
+ case PGRES_COMMAND_OK:
+ break;
+ case PGRES_TUPLES_OK:
+ printResultSet(res);
+ break;
+ case PGRES_FATAL_ERROR:
+ /* Detail may contain xid values, so just show
primary. */
+ printf("%s: %s\n", PQresultErrorField(res,
PG_DIAG_SEVERITY),
+ PQresultErrorField(res,
PG_DIAG_MESSAGE_PRIMARY));
+ break;
+ default:
+ printf("unexpected result status: %s\n",
+ PQresStatus(PQresultStatus(res)));
+ }
+ PQclear(res);
+ }
+
+ return 1;
+ }
+
static void
printResultSet(PGresult *res)
{
*** /dev/null
--- b/src/test/isolation/specs/fk-contention.spec
***************
*** 0 ****
--- 1,19 ----
+ setup
+ {
+ CREATE TABLE foo (a int PRIMARY KEY, b text);
+ CREATE TABLE bar (a int NOT NULL REFERENCES foo);
+ INSERT INTO foo VALUES (42);
+ }
+
+ teardown
+ {
+ DROP TABLE foo, bar;
+ }
+
+ session "s1"
+ setup { BEGIN; }
+ step "ins" { INSERT INTO bar VALUES (42); }
+ step "com" { COMMIT; }
+
+ session "s2"
+ step "upd" { UPDATE foo SET b = 'Hello World'; }
*** /dev/null
--- b/src/test/isolation/specs/fk-deadlock.spec
***************
*** 0 ****
--- 1,54 ----
+ setup
+ {
+ CREATE TABLE parent (
+ parent_key int PRIMARY KEY,
+ aux text NOT NULL
+ );
+
+ CREATE TABLE child (
+ child_key int PRIMARY KEY,
+ parent_key int NOT NULL REFERENCES parent
+ );
+
+ INSERT INTO parent VALUES (1, 'foo');
+ }
+
+ teardown
+ {
+ DROP TABLE parent, child;
+ }
+
+ session "s1"
+ setup { BEGIN; }
+ step "s1i" { INSERT INTO child VALUES (1, 1); }
+ step "s1u" { UPDATE parent SET aux = 'bar'; }
+ step "s1c" { COMMIT; }
+
+ session "s2"
+ setup { BEGIN; }
+ step "s2i" { INSERT INTO child VALUES (2, 1); }
+ step "s2u" { UPDATE parent SET aux = 'baz'; }
+ step "s2c" { COMMIT; }
+
+ ## Most theoretical permutations require that a blocked session execute a
+ ## command, making them impossible in practice.
+ permutation "s1i" "s1u" "s1c" "s2i" "s2u" "s2c"
+ permutation "s1i" "s1u" "s2i" "s1c" "s2u" "s2c"
+ #permutation "s1i" "s1u" "s2i" "s2u" "s1c" "s2c"
+ #permutation "s1i" "s1u" "s2i" "s2u" "s2c" "s1c"
+ #permutation "s1i" "s2i" "s1u" "s1c" "s2u" "s2c"
+ permutation "s1i" "s2i" "s1u" "s2u" "s1c" "s2c"
+ #permutation "s1i" "s2i" "s1u" "s2u" "s2c" "s1c"
+ #permutation "s1i" "s2i" "s2u" "s1u" "s1c" "s2c"
+ permutation "s1i" "s2i" "s2u" "s1u" "s2c" "s1c"
+ #permutation "s1i" "s2i" "s2u" "s2c" "s1u" "s1c"
+ #permutation "s2i" "s1i" "s1u" "s1c" "s2u" "s2c"
+ permutation "s2i" "s1i" "s1u" "s2u" "s1c" "s2c"
+ #permutation "s2i" "s1i" "s1u" "s2u" "s2c" "s1c"
+ #permutation "s2i" "s1i" "s2u" "s1u" "s1c" "s2c"
+ permutation "s2i" "s1i" "s2u" "s1u" "s2c" "s1c"
+ #permutation "s2i" "s1i" "s2u" "s2c" "s1u" "s1c"
+ #permutation "s2i" "s2u" "s1i" "s1u" "s1c" "s2c"
+ #permutation "s2i" "s2u" "s1i" "s1u" "s2c" "s1c"
+ permutation "s2i" "s2u" "s1i" "s2c" "s1u" "s1c"
+ #permutation "s2i" "s2u" "s2c" "s1i" "s1u" "s1c"
*** /dev/null
--- b/src/test/isolation/specs/fk-deadlock2.spec
***************
*** 0 ****
--- 1,59 ----
+ setup
+ {
+ CREATE TABLE A (
+ AID integer not null,
+ Col1 integer,
+ PRIMARY KEY (AID)
+ );
+
+ CREATE TABLE B (
+ BID integer not null,
+ AID integer not null,
+ Col2 integer,
+ PRIMARY KEY (BID),
+ FOREIGN KEY (AID) REFERENCES A(AID)
+ );
+
+ INSERT INTO A (AID) VALUES (1);
+ INSERT INTO B (BID,AID) VALUES (2,1);
+ }
+
+ teardown
+ {
+ DROP TABLE a, b;
+ }
+
+ session "s1"
+ setup { BEGIN; }
+ step "s1u1" { UPDATE A SET Col1 = 1 WHERE AID = 1; }
+ step "s1u2" { UPDATE B SET Col2 = 1 WHERE BID = 2; }
+ step "s1c" { COMMIT; }
+
+ session "s2"
+ setup { BEGIN; }
+ step "s2u1" { UPDATE B SET Col2 = 1 WHERE BID = 2; }
+ step "s2u2" { UPDATE B SET Col2 = 1 WHERE BID = 2; }
+ step "s2c" { COMMIT; }
+
+ ## Many theoretical permutations require that a blocked session execute a
+ ## command, making them impossible in practice.
+ permutation "s1u1" "s1u2" "s1c" "s2u1" "s2u2" "s2c"
+ permutation "s1u1" "s1u2" "s2u1" "s1c" "s2u2" "s2c"
+ #permutation "s1u1" "s1u2" "s2u1" "s2u2" "s1c" "s2c"
+ #permutation "s1u1" "s1u2" "s2u1" "s2u2" "s2c" "s1c"
+ #permutation "s1u1" "s2u1" "s1u2" "s1c" "s2u2" "s2c"
+ permutation "s1u1" "s2u1" "s1u2" "s2u2" "s1c" "s2c"
+ permutation "s1u1" "s2u1" "s1u2" "s2u2" "s2c" "s1c"
+ permutation "s1u1" "s2u1" "s2u2" "s1u2" "s1c" "s2c"
+ permutation "s1u1" "s2u1" "s2u2" "s1u2" "s2c" "s1c"
+ #permutation "s1u1" "s2u1" "s2u2" "s2c" "s1u2" "s1c"
+ #permutation "s2u1" "s1u1" "s1u2" "s1c" "s2u2" "s2c"
+ permutation "s2u1" "s1u1" "s1u2" "s2u2" "s1c" "s2c"
+ permutation "s2u1" "s1u1" "s1u2" "s2u2" "s2c" "s1c"
+ permutation "s2u1" "s1u1" "s2u2" "s1u2" "s1c" "s2c"
+ permutation "s2u1" "s1u1" "s2u2" "s1u2" "s2c" "s1c"
+ #permutation "s2u1" "s1u1" "s2u2" "s2c" "s1u2" "s1c"
+ #permutation "s2u1" "s2u2" "s1u1" "s1u2" "s1c" "s2c"
+ #permutation "s2u1" "s2u2" "s1u1" "s1u2" "s2c" "s1c"
+ permutation "s2u1" "s2u2" "s1u1" "s2c" "s1u2" "s1c"
+ #permutation "s2u1" "s2u2" "s2c" "s1u1" "s1u2" "s1c"
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
