Re: [HACKERS] Re: Referential Integrity In PostgreSQL

Andreas Zeugswetter wrote:
> 
> > 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.

Modified tuples are locked by the fact that t_xmin/t_xmax
is/are in-progress. DIRTY READ allows to see that tuples
is being modified. I supposed to use some function to wait
concurrent transaction commit/abort.

> 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)

tx2 can't commit till tx1 is in-progress, right?
More of that, if tx2 is in serializable mode and there was
no PK committed before tx2 began (i.e. visible to tx2 queries)
then this should result in tx2 abort, imho.

> 
> I don't think we can read dirty.
> 
> We have to wait for PK lock, and decide after tx1 commited/rolled back.

Yes, but select _never_ waits and READ COMMITTED never sees
uncommitted concurrent changes. That's why I propose to use
DIRTY READ (to see) and function (to wait).

If two tx-s will wait one another then one of them will be
aborted (deadlock condition) and another will continue to
perform constraint check.

> On timeout we decide as follows:

Why timeout should be used?

> Even if above tx1 (insert PK) is committed later,
> we throw an error for tx2 (insert FK).

I'm not sure that we should abort tx2 running in READ COMMITTED
mode.

> 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.

Why should we abort tx2 if pk is deleted but uncommitted yet?
In the case of tx1 (delete pk) abort we could let tx2 inserts
fk. Why not?

> A lock mode wait 0 would be convenient here.
> 
> Everything else imho leads to a violated integrity.

Vadim