description: SFID: 973159#⁠
Submitted By: johnsparrowuk

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
<> http://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.

=>

SFID: 973159#⁠
Submitted By: johnsparrowuk

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
<> http://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.

Workflow: jira [ 10494 ] => Firebird [ 14786 ]