Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
> On 5/11/10 4:11 PM +0300, I wrote:
>> I took the "SELECT ... FOR SHARE" suggestion in a more general way,
>> suggesting the use of row-level locks. T2 should be holding an
>> exclusive row-level lock (SELECT ... FOR UPDATE) when checking for
>> references.
> Hmm. Right, that transaction wouldn't see the rows in a serializable
> transaction so this doesn't solve the problem.
Yeah. The hidden "magic" in the built-in FK code is not locking
(it does actually use SELECT FOR SHARE to lock rows). Rather, it's
about doing tuple liveness checks using snapshots that aren't available
at the SQL level, particularly in serializable transactions.
regards, tom lane