Ann,

Of course I know how duplictes in index are checked using "system context" :-)
I was asking what is expected behaviour when "*check constraint* operate
in system context", i.e. whether select statement used in that constraint should
see the latest version or all versions of each record.

>From your reply I deduced that all versions.

Here is example I already sent:
* we have table without index
(but even if there is index, it will not be used in selects from previous examples)
* transaction A inserts row (e.g. 10), starts savepoint, and update that row (e.g. to 20)
* transaction B now CAN insert value 10, because at this point there is NO other
record version with value 10 (with one minor exception there is always
only one record version per transaction), so select in check constraint will not
find anything, even if it can see all record versions
* transaction A just rollback to its savepoint and commits - and you have duplicates again.

So I still think that check constraints operating in system context
can't be used to ensure uniqueness, pk-fk relationships (as pointed out Jerome), etc.
at least without some other modifications (but I have no idea what they should be).

Ivan

----- Original Message -----
From: "Ann W. Harrison" <aharrison@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, June 15, 2004 9:12 PM
Subject: Re: CHECK Constraints (was: Re: [firebird-support] Re: Checking periods don't overlap)


> At 02:41 PM 6/15/2004, Ivan Prenosil wrote:
>
> >Still nobody explained what "operate in system transaction" or
> >"operate in system context" or "dirty read" or "potato" means in the context
> >of this discussion, nor tried to list
> >advantages/disadvantages/consequences of it.
>
> Ah. When an entry is made into a unique index, the code that
> checks to see whether there are duplicates "visible to any
> active transaction" is running in "system context" or equivalently
> as "in the system transaction". It's really not a "dirty read",
> since it sees both committed values and uncommitted values. A
> "dirty read" in my vocabulary sees only the latest version.
>
> >If yes then here is what will happen in such case when running slightly
> >changed example...
>
> In your example, the constraint is that there be no duplicates.
> An uncommitted transaction has changed a 1 to a 5. A concurrent
> transaction attempts to store a 1. The system transaction would
> recognize that the change is uncommitted and return an error to
> the storing transaction.
>
> Regards,
>
>
> Ann