Re: [HACKERS] Re: Referential Integrity In PostgreSQL

Jan Wieck wrote:
> 
> > It is my understanding, that the RI check is performed on the newest
> > available (committed) data (+ modified data from my own tx).
> > E.g. a primary key that has been removed by another transaction after
> > my begin work will lead to an RI violation if referenced as foreign key.
> 
>     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...

BTW, using triggers to check _each_ modified tuple
(i.e. run Executor for each modified tuple) is bad for
performance. We could implement direct support for
standard RI constraints.

Using rules (statement level triggers) for INSERT...SELECT,
UPDATE and DELETE queries would be nice! Actually, RI constraint
checks need in very simple queries (i.e. without distinct etc)
and the only we would have to do is

>     What I'm not sure about is which snapshot to use to  get  the
>     OLD  tuples  (outdated  in  this  transaction  by  a previous
>     command). Vadim?

1. Add CommandId to Snapshot.
2. Use Snapshot->CommandId instead of global CurrentScanCommandId.
3. Use Snapshots with different CommandId-s to get OLD/NEW  versions.

But I agreed that the size of parsetrees may be big and for
COPY...FROM/INSERTs we should remember IDs of modified
tuples. Well. Please remember that I implement WAL right
now, already have 1000 lines of code and hope to run first
tests after writing additional ~200 lines -:)
We could read modified tuple IDs from WAL...

Vadim