[Firebird-devel] [ firebird-Bugs-973159 ] Check constraints don't operate in system context

[SourceForge.net <no...@so...>]

Bugs item #973159, was opened at 2004-06-15 11:00
Message generated for change (Comment added) made by johnsparrowuk
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=973159&group_id=9028

Category: Core Engine
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: John Sparrow (johnsparrowuk)
Assigned to: Nobody/Anonymous (nobody)
>Summary: Check constraints don't operate in system context

Initial Comment:
Check constraints only see committed data from other 
transactions, when they should dirty-read to be 
consistent with index behaviour (operate in system 
transaction context?).
For example:

create table T (pk integer not null primary key, i integer 
not null);

ALTER TABLE T ADD CONSTRAINT Unique_I_T CHECK 
(not exists (select pk from T where new.i = T.i and pk 
<> new.pk)

(snapshot transactions)
Trans 1:
insert into T values (1,1); WORKS FINE

Trans 2:
insert into T values (2,2); WORKS FINE
insert into T values (3,2); CHECK VIOL, COOL!
insert into T values (3,1); WORKS, OH DEAR!
commit

Trans 1:
commit

Start new trans:
select * from T
1,1
2,2
3,1   (violation of check constraint)

Example is simplistic, but more of a problem when 
checking for overlapping periods (for example) which 
can't have unique indexes applied.

----------------------------------------------------------------------

>Comment By: John Sparrow (johnsparrowuk)
Date: 2004-06-15 16:37

Message:
Logged In: YES 
user_id=205339

Appologies for using the term 'dirty read'! I mean 'operate in 
system context'. You say Potato, I say...
John

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=973159&group_id=9028