Sign up ×
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. It's 100% free, no registration required.

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()

    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.*);
        RAISE EXCEPTION 'Bigtable insert date is out of range!';
    END IF;

LANGUAGE plpgsql;


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.

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