Re: FOR KEY LOCK foreign keys

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