Re: Re: [COMMITTERS] pgsql: Add some isolation tests for deadlock detection and resolution.

We're not out of the woods on this :-( ... jaguarundi, which is the first
of the CLOBBER_CACHE_ALWAYS animals to run these tests, didn't like them
at all.  I think I fixed the deadlock-soft-2 failure, but its take on
deadlock-hard is:

*** 17,25 **** step s6a7: LOCK TABLE a7; <waiting ...> step s7a8: LOCK TABLE a8; <waiting ...> step s8a1: LOCK TABLE
a1;<waiting ...>
 
- step s8a1: <... completed> step s7a8: <... completed>
! error in steps s8a1 s7a8: ERROR:  deadlock detected step s8c: COMMIT; step s7c: COMMIT; step s6a7: <... completed>
--- 17,25 ---- step s6a7: LOCK TABLE a7; <waiting ...> step s7a8: LOCK TABLE a8; <waiting ...> step s8a1: LOCK TABLE
a1;<waiting ...> step s7a8: <... completed>
 
! step s8a1: <... completed>
! ERROR:  deadlock detected step s8c: COMMIT; step s7c: COMMIT; step s6a7: <... completed>

The problem here is that when the deadlock detector kills s8's
transaction, s7a8 is also left free to proceed, so there is a race
condition as to which query completion will get back to
isolationtester first.

One grotty way to handle that would be something like

-step "s7a8"    { LOCK TABLE a8; }
+step "s7a8"    { LOCK TABLE a8; SELECT pg_sleep(5); }

Or we could simplify the locking structure enough so that no other
transactions are released by the deadlock failure.  I do not know
exactly what you had in mind to be testing here?
        regards, tom lane