On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera
<alvhe...@commandprompt.com> wrote:
> Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011:
>> Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011:
>
>> > I think that you also need to update the constraint exclusion code
>> > (get_relation_constraints() or nearby), otherwise the planner might
>> > exclude a relation on the basis of a CHECK constraint that is not
>> > currently VALID.
>>
>> Ouch, yeah, thanks for pointing that out. Fortunately the patch to fix
>> this is quite simple. I don't have it handy right now but I'll post it
>> soon.
>
> Here's the complete patch.
>
psql \h says (among other things) for ALTER TABLE
"""
ADD table_constraint
ADD table_constraint_using_index
ADD table_constraint [ NOT VALID ]
"""
ADD table_constraint appears twice and isn't true that all
table_constraint accept the NOT VALID syntax... maybe we can accpet
the syntax and send an unimplemented feature message for the other
table_constraints?
attached, is a script with the examples i have tried:
EXAMPLE 1:
constraint_exclusion when using NOT VALID check constraints... and it
works well, except when the constraint has been validated, it keeps
ignoring it (which means i won't benefit from constraint_exclusion)
until i execute ANALYZE on the table or close connection
EXAMPLE 2:
if i have a DOMAIN with a NOT VALID check constraint, and i use it as
the new type of a column it checks the constraint
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
/* example 1 */
DROP TABLE IF EXISTS padre CASCADE;
create table padre(i serial primary key, d date);
create table hija_2010 () inherits (padre);
create table hija_2011 () inherits (padre);
insert into hija_2010(d) values ('2011-08-15'::date);
insert into hija_2011(d) values ('2011-09-15'::date);
alter table hija_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
alter table hija_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date;
create table hija_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (padre);
insert into hija_2009(d) values ('2009-06-13');
explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date;
explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date;
alter table hija_2011 VALIDATE CONSTRAINT hija_2011_d_check;
explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date;
/* example 2 */
create domain mes as int;
create table t_mes (m mes);
insert into t_mes values(13);
alter domain mes add check (value between 1 and 12) not valid;
create table t_mes2 (m int);
insert into t_mes2 values(13);
alter table t_mes2 ALTER m type mes;
ERROR: value for domain mes violates check constraint "mes_check"
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
