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
fromD2;  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@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support