BUG #10254: Joined Constraints not invoked on date ranges

From: christopher(dot)hamel(at)zimmer(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #10254: Joined Constraints not invoked on date ranges
Date: 2014-05-07 15:24:36
Message-ID: 20140507152436.1397.689@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 10254
Logged by: Christopher Hamel
Email address: christopher(dot)hamel(at)zimmer(dot)com
PostgreSQL version: 9.3.0
Operating system: RedHat 6.4
Description:

If you have this theoretical structure:

create table stage.header (
id int not null,
transaction_date date not null
);

create table stage.line (
header_id int not null,
transaction_date date not null,
line_id int not null
);

create table stage.header_2013 (
constraint header_2013_ck1 check (transaction_date >= '2013-01-01' and
transaction_date < '2014-01-01')
) inherits (stage.header);

create table stage.header_2014 (
constraint header_2014_ck1 check (transaction_date >= '2014-01-01' and
transaction_date < '2015-01-01')
) inherits (stage.header);

create table stage.line_2013 (
constraint line_2013_ck1 check (transaction_date >= '2013-01-01' and
transaction_date < '2014-01-01')
) inherits (stage.line);

create table stage.line_2014 (
constraint line_2014_ck1 check (transaction_date >= '2014-01-01' and
transaction_date < '2015-01-01')
) inherits (stage.line);

If I run an explain plan on the following query:

select *
from
stage.header h
join stage.line l on
h.id = l.header_id and
h.transaction_date = l.transaction_date
where
h.transaction_date = '2014-03-01'

It correctly invokes the check constraint on both h and l and only reads the
"2014" tables.

However, if I change the "= 2014-03-01" to "> 2014-03-01" the check
constraint is ignored.

If I specifically invoke the range on both the h and l tables, it will work
fine, but since the join specifies those fields have to be the same, can
that condition be propagated automatically?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephen Frost 2014-05-07 15:44:31 Re: BUG #10250: pgAdmin III 1.16.1 stores unescaped plaintext password
Previous Message Tom Lane 2014-05-07 14:15:24 Re: Server process crash - Segmentation fault