On Tuesday 30 December 2008 19:28:01 Kevin Grittner wrote: > Here we go. I've labeled the transactions consistently with new > thread I started trying to characterize the full scope of issues and > workarounds.
OK, I believe it now. :-) The missing predicate locking is again at fault here. Because this ... > -- connection 1 (start of T0) > start transaction isolation level serializable; > insert into receipt values (3, (select deposit_date from ctl where k = > 'receipt'), 4.00); ... would lock ctl where k = 'receipt' ... > -- connection 2 (T1) > start transaction isolation level serializable; > update ctl set deposit_date = date '2008-12-23' where k = 'receipt'; > commit transaction; ... and then this would have to wait ... > -- connection 2 (TN version 2) > start transaction isolation level serializable; > insert into receipt_totals > select r.deposit_date, c.deposit_date, sum(amount) > from ctl c join receipt r > on ( r.deposit_date < c.deposit_date > and not exists > ( > select * from receipt r2 > where r2.deposit_date < c.deposit_date > and r2.deposit_date > r.deposit_date > ) > ) > group by r.deposit_date, c.deposit_date; > commit transaction; ... and this could never be executed before T0 commits. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
