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