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

Oh - I see - yes.

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

As I want to implement it, there would be not much difference
between a regular trigger invocation and a deferred one. If
that causes a performance problem, I think we should speed up
the trigger call mechanism in general instead of not using
triggers.

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

Not only on COPY. One regular INSERT/UPDATE/DELETE statement
can actually fire thousands of trigger calls right now. These
triggers normally use SPI to execute their own queries. If
such a trigger now uses a query that in turn causes a
deferred constraint, we might have to save thousands of
deferred querytrees - impossible mission.

That's IMHO a clear drawback against using rules for
deferrable RI.

What I'm currently doing is clearly encapsulated in some
functions in commands/trigger.c (except for some additional
attributes in pg_trigger). If it later turns out that we can
combine the information required into WAL, I think we have
time enough to do so and shouldn't really care if v6.6
doesn't have it already combined.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #