Dear Helen,

I think most of our misunderstandings here are due to our different
assumptions.

I was rather on Alex's position (although I did not understand all its
subtleties before he explained me) which, apparently, you disagree with.

He says "Declarative RI is performed *out of transaction context* in
_most pessimistic mode_"
when you say
"[decl.] RI works in the current transaction context".

I just made some tests to reproduce the scenarii exposed by Alex. They tend
to show Alex is right: Decl. RI really seems to work out of tx context.
And it's well so, because following cases I-b) and II-b), I were able to
break my db integrity (orphaned details) using RI triggers when decl. RI
doesn't permit this.

And yes, as Alex explains, read_committed doesn't prevent from anything
unlike I first thought. The matter is really "Decl. RI vs triggers" as
Claudio titled its paper.

All this explains why, since I never used RI triggers, I asked about their
potential "danger" . (static tables aside)

<..>

> I've emailed Ann and asked her to look over the discussion and
> give us one of her famous lucid commentaries. :-)

I am eager to read her.

<..>

> I still can't get at your reasoning for this and why you think
> readcommitted is a solution to some perceived problem.

Right. I was wrong assuming read_committed would be a solution. But I still
perceive the problem. <g>

> Readcommitted + no
> record version + lock wait *does* give you a window where you can stuff

up

> someone else's pending work, or someone else's same settings will stuff up
> yours;

Alex' point I-b) looks like that window to me.



Regarding CHECKs ...

<..>

> >But in the following constraint
> >
> >ALTER TABLE PERSONNEL ADD CONSTRAINT CHK_PERSONNEL CHECK (start_date <
> >end_date);
> >
> >"start_date" and "end_date" are in context. Right ?
>
> Yes - because CHECK constraints are table-level constraints.

What do you mean by table-level constraints ? This simple CHECK is evaluated
*once* against the current in-context variables "start_date" and "end_date".
Just like a trigger would evaluate NEW.start_date and NEW.end_date. Right ?

> >I was hoping to use the same in-context variables in a select-form CHECK.
> >Didn't succeed.
>
> What's a "select-from CHECK"? Are you talking about a subquery on another
> table? (the one I recommended not to do)?

Hmm. The CHECK (tested before any recommendation on your part ;-)) looked
like this :

ALTER TABLE TEST_CHK ADD CONSTRAINT CHK_TEST_CHK2 check
(not exists (
select ID
from TEST_CHK T1
where T1.SSN = SSN and T1.PCN= PCN
and T1.START_DATE < END_DATE
and START_DATE < T1.END_DATE
and T1.ID <> ID
));

I hoped FB would have seen in-context variables where the prefix T1 isn't
used, but no. :( .
w/o prefix doesn't change anything. This CHECK gets useless because it
doesn't have the intended meaning.

Well, it was just a test. I'll leave select-form CHECKs aside until I know
how to use them.


Thanks for your patience Helen and for bearing with my english.

--
Jerome