> Oh - well - vacuum shouldn't touch  relations  where
> deferred   triggers  are  outstanding.   Might  require  some
> special lock entry - Vadim?

All modified data will be in this same still open transaction.
Therefore no relevant data can be removed by vacuum anyway.

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.

Andreas

Tue, 21 Sep 1999 11:24:09 +0200
Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
Re: [HACKERS] Re: Referential Integrity In  PostgreSQL
>
> > Oh - well - vacuum shouldn't touch  relations  where
> > deferred   triggers  are  outstanding.   Might  require  some
> > special lock entry - Vadim?
>
> All modified data will be in this same still open transaction.
> Therefore no relevant data can be removed by vacuum anyway.

    I expect this, but I really need to be sure that not even the
    location of the tuple in the heap will change. I need to find
    the tuples at the time the deferred triggers must be executed
    via heap_fetch() by their CTID!

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

    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?


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



Tue, 21 Sep 1999 13:39:27 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
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

Tue, 21 Sep 1999 22:33:20 +0800
Vadim Mikheev <vadim@krs.ru>
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@debis.com (Jan Wieck) #



Tue, 21 Sep 1999 16:55:33 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: Referential Integrity In  PostgreSQL
Jan Wieck wrote:
> 
> > 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
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^
Yes, because of we have not Statement Level Triggers (SLT).
Deferred SLT would require us to remember _one_ parsertree for each
statement, just like deferred rules.

>     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.
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Why should we save _thousands_ of querytrees in the case
of row level trigger (I assume you mean one querytree for 
each modified tuple)?
As I described in prev letter - we have to remember just
LastCommandIdProccessedByConstraint to stop fetching
tuples from WAL.

BTW, this is what sql3-12aug93 says about triggers and RI:

22)If the <trigger event> specifies UPDATE, then let Ci be the i-th  
   <column name> in the <trigger column list>.
                      /* i.e UPDATE OF C1,..Cj */
   T shall not be the referencing table in any <referential 
   constraint definition> that specifies ON UPDATE CASCADE, 
   ON UPDATE SET NULL, ON UPDATE SET DEFAULT, ON DELETE SET NULL, 
   or ON DELETE SET DEFAULT and contains a <reference column list> 
   that includes Ci.

Interesting?

Vadim

Wed, 22 Sep 1999 00:13:13 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Re: Referential Integrity In  PostgreSQL