Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on.

The insert trigger works fine and new rows are inserted into the correct tables. The SELECT however runs over all tables regardless of my WHERE clause.

Here is my config:

constraint_exclusion = on (both in postgresql.conf and also tried with "ALTER DATABASE bigtable SET constraint_exclusion=on;")

Master Table:

CREATE TABLE bigtable (
    id bigserial NOT NULL,
    userid integer NOT NULL,
    inserttime timestamp with time zone NOT NULL DEFAULT now()
)

Child Table 1:

CREATE TABLE bigtable_2013_11 (CHECK ( inserttime >= DATE '2013-11-01' AND inserttime < DATE '2013-12-01' )) INHERITS (bigtable);        

Child Table 2:

CREATE TABLE bigtable_2013_12 (CHECK ( inserttime >= DATE '2013-12-01' AND inserttime < DATE '2014-01-01' )) INHERITS (bigtable);    

Stored Procedure:

CREATE OR REPLACE FUNCTION bigtable_insert_function()
RETURNS TRIGGER AS $$
BEGIN

    IF ( NEW.inserttime >= DATE '2013-11-01' AND NEW.inserttime < DATE '2013-11-01' ) THEN
        INSERT INTO bigtable_2013_11 VALUES (NEW.*);
    ELSEIF (NEW.inserttime >= DATE '2013-12-01' AND NEW.inserttime < DATE '2014-01-01' ) THEN
        INSERT INTO bigtable_2013_12 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Bigtable insert date is out of range!';
    END IF;

    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER bigtable_insert_trigger BEFORE INSERT ON bigtable FOR EACH ROW EXECUTE PROCEDURE bigtable_insert_function();

It's pretty much the text book setup. The insert works fine:

INSERT INTO bigtable (userid, inserttime) VALUES ('1', now());

Above insert results in the new row being inserted correctly into 'bigtable_2013_11' only.

However I can't get the SELECT to exclude the irrelevant tables. All SELECTs always run over all tables. I would expect bigtable_2013_12 to be excluded when following SELECT queries are used:

SELECT * FROM bigtable WHERE inserttime >= DATE '2013-11-01'::date AND inserttime < '2013-12-01'::date;

SELECT * FROM bigtable WHERE EXTRACT(MONTH FROM inserttime) = 11 AND EXTRACT (YEAR FROM inserttime) = 2013;

However the result is always this:

"Result  (cost=0.00..68.90 rows=17 width=20)"
"  ->  Append  (cost=0.00..68.90 rows=17 width=20)"
"        ->  Seq Scan on bigtable  (cost=0.00..0.00 rows=1 width=20)"
"              Filter: ((inserttime >= '2013-11-02'::date) AND (inserttime < '2013-11-30'::date))"
"        ->  Seq Scan on bigtable_2013_11 bigtable  (cost=0.00..34.45 rows=8 width=20)"
"              Filter: ((inserttime >= '2013-11-02'::date) AND (inserttime < '2013-11-30'::date))"
"        ->  Seq Scan on bigtable_2013_12 bigtable  (cost=0.00..34.45 rows=8 width=20)"
"              Filter: ((inserttime >= '2013-11-02'::date) AND (inserttime < '2013-11-30'::date))"

Why are my checks not kicking in? I am out of ideas. Everything seems to be setup correctly. Did I miss anything? Any help would be greatly appreciated.

share|improve this question

closed as off-topic by dezso, bluefeet, Max Vernon, RolandoMySQLDBA, Paul White Nov 5 '13 at 5:49

If this question can be reworded to fit the rules in the help center, please edit the question.

1  
So it turns out it has something to do with the timestamp/date conversions here. Denis over at stackoverflow helped me out: stackoverflow.com/questions/19759947/… (Too low rep to answer this question. Posting as comment instead) – marcinx Nov 4 '13 at 0:55
4  
This question appears to be off-topic because it is already answered on StackOverflow – dezso Nov 4 '13 at 7:10