Folks,
Bob Ippolito found this while testing Bizgres.
It *seems* like our smarter type coercion rules do not apply when
constraints are being generated. That is, the types of constants in
constraints, if not coerced, still default to the old "dumb" casting where
the type of the comparing column isn't checked.
This is visible if you run a simple test on constraint exclusion:
CE not used
----------------------------
set constraint_exclusion=on;
create table a ( a bigint, b text );
create table a1 () inherits (a);
create table a2 () inherits (a);
create table a3 () inherits (a);
alter table a1 add constraint a1_a check ( a between 1 and 3);
alter table a2 add constraint a2_a check ( a between 4 and 6);
alter table a3 add constraint a3_a check ( a between 7 and 9);
insert into a1 values ( 1, 'B' );
insert into a2 values ( 5, 'F' );
insert into a3 values ( 8, 'G' );
explain analyze select * from a where a.a between 5 and 6;
CE used
---------------------------------
create table a ( a bigint, b text );
create table a1 () inherits (a);
create table a2 () inherits (a);
create table a3 () inherits (a);
alter table a1 add constraint a1_a check ( a between 1::BIGINT and
3::BIGINT);
alter table a2 add constraint a2_a check ( a between 4::BIGINT and
6::BIGINT);
alter table a3 add constraint a3_a check ( a between 7::BIGINT and
9::BIGINT);
insert into a1 values ( 1, 'B' );
insert into a2 values ( 5, 'F' );
insert into a3 values ( 8, 'G' );
explain analyze select * from a where a.a between 5 and 6;
So, is this a real bug in constraints or does the problem lie somewhere
else? Is it fixable?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco