Re: FOR KEY LOCK foreign keys

Excerpts from Noah Misch's message of mié jul 13 01:34:10 -0400 2011:

> coypu failed during the run of the test due to a different session being chosen
> as the deadlock victim. We can now vary deadlock_timeout to prevent this; see
> attached fklocks-tests-deadlock_timeout.patch. This also makes the tests much
> faster on a default postgresql.conf.

I applied your patch, thanks. I couldn't reproduce the failures without
it, even running only the three new tests in a loop a few dozen times.

> crake failed when it reported waiting on the first step of an existing isolation
> test ("two-ids.spec"). I will need to look into that further.

Actually, there are four failures in tests other than the two fixed by
your patch. These are:

http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=crake&dt=2011-07-12%2022:32:02
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=nightjar&dt=2011-07-14%2016:27:00
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=pitta&dt=2011-07-15%2015:00:08
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=crake&dt=2011-07-15%2018:32:02

The last two are an identical failure in multiple-row-versions:
***************
*** 1,11 ****
Parsed test spec with 4 sessions

starting permutation: rx1 wx2 c2 wx3 ry3 wy4 rz4 c4 c3 wz1 c1
! step rx1: SELECT * FROM t WHERE id = 1000000;
id txt

1000000
- step wx2: UPDATE t SET txt = 'b' WHERE id = 1000000;
step c2: COMMIT;
step wx3: UPDATE t SET txt = 'c' WHERE id = 1000000;
step ry3: SELECT * FROM t WHERE id = 500000;
--- 1,12 ----
Parsed test spec with 4 sessions

starting permutation: rx1 wx2 c2 wx3 ry3 wy4 rz4 c4 c3 wz1 c1
! step rx1: SELECT * FROM t WHERE id = 1000000; <waiting ...>
! step wx2: UPDATE t SET txt = 'b' WHERE id = 1000000;
! step rx1: <... completed>
id txt

1000000
step c2: COMMIT;
step wx3: UPDATE t SET txt = 'c' WHERE id = 1000000;
step ry3: SELECT * FROM t WHERE id = 500000;

The other failure by crake in two-ids:

***************
*** 440,447 ****
step c3: COMMIT;

starting permutation: rxwy2 wx1 ry3 c2 c3 c1
! step rxwy2: update D2 set id = (select id+1 from D1);
step wx1: update D1 set id = id + 1;
step ry3: select id from D2;
id

--- 440,448 ----
step c3: COMMIT;

starting permutation: rxwy2 wx1 ry3 c2 c3 c1
! step rxwy2: update D2 set id = (select id+1 from D1); <waiting ...>
step wx1: update D1 set id = id + 1;
+ step rxwy2: <... completed>
step ry3: select id from D2;
id

And the most problematic one, in nightjar, is a failure to send two
async commands, which is not supported by the new code:

--- 255,260 ----
ERROR: could not serialize access due to read/write dependencies among transactions

starting permutation: ry2 wx2 rx1 wy1 c2 c1
! step ry2: SELECT count(*) FROM project WHERE project_manager = 1; <waiting ...>
! failed to send query: another command is already in progress

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support