Re: [HACKERS] Re: Referential Integrity In PostgreSQL

Vadim wrote:
> >     Absolutely right. The function that will  fire  the  deferred
> >     triggers  must  switch to READ COMMITTED isolevel while doing
>                                 ^^^^^^^^^^^^^^
> >     so.
>
> NO!
> What if one transaction deleted PK, another one inserted FK
> and now both performe RI check? Both transactions _must_
> use DIRTY READs to notice that RI violated by another
> in-progress transaction and wait for concurrent transaction...

I think we need some kind of lock on the PK table row.
This is because a rollback must allways work. 
(If tx1 (insert PK) wants a rollback after tx2 did insert FK 
this cannot throw a RI Violation) 

I don't think we can read dirty.

We have to wait for PK lock, and decide after tx1 commited/rolled back.

On timeout we decide as follows:
Even if above tx1 (insert PK) is committed later, 
we throw an error for tx2 (insert FK).
Also if a pk row is deleted/updated/inserted but not committed yet, 
we ignore both old and new value for the FK check of tx2
after timeout and violate tx2.

A lock mode wait 0 would be convenient here.

Everything else imho leads to a violated integrity.

Andreas