Re: [HACKERS] Re: Referential Integrity In PostgreSQL - Mailing list pgsql-hackers

>
> 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@debis.com (Jan Wieck) #