Constraint Type Coercion issue? - Mailing list pgsql-hackers

From Josh Berkus
Subject Constraint Type Coercion issue?
Date
Msg-id 200509141048.34953.josh@agliodbs.com
Whole thread Raw
Responses Re: Constraint Type Coercion issue?
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Spinlocks, yet again: analysis and proposed patches
Next
From: Tom Lane
Date:
Subject: Re: About method of PostgreSQL's Optimizer