Обсуждение: 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. 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
>
> > 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) #
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
>
> 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) #
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
DELETESET DEFAULT and contains a <reference column list> that includes Ci.
Interesting?
Vadim