Thread: BUG #3235: Partitioning has problem with timestamp and timestamptz data types

The following bug has been logged online:

Bug reference:      3235
Logged by:          Christian Gonzalez
Email address:      christian.gonzalez@sigis.com.ve
PostgreSQL version: 8.2.1
Operating system:   Red Hat 4.1.1-30
Description:        Partitioning has problem with timestamp and timestamptz
data types
Details:

When you use timestamp and timestamptz data type for partitioning
implementation,
your postgresql partitioning  implementation doesen't work fine when you
make a
SELECT using this columns type.

Using Example in PostgreSQL Partitioning page
http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html

--Create Master Table
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

--Create Child Tables
CREATE TABLE measurement_y2004m02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
    CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m11 (
    CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
    CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
    CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);

-- Add two new column (timestamp and timestamptz)
ALTER TABLE measurement ADD COLUMN logdatet timestamp;
ALTER TABLE measurement ADD COLUMN logdatett timestamptz;

-- Test SELECT in column type DATE
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdate = '2006-01-01'
"Result  (cost=0.00..50.75 rows=12 width=32)"
"  ->  Append  (cost=0.00..50.75 rows=12 width=32)"
"        ->  Seq Scan on measurement  (cost=0.00..25.38 rows=6 width=32)"
"              Filter: (logdate = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..25.38
rows=6 width=32)"
"              Filter: (logdate = '2006-01-01'::date)"

-- Test SELECT in column type timestamp
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdatet = '2006-01-01'
"Result  (cost=0.00..152.25 rows=36 width=32)"
"  ->  Append  (cost=0.00..152.25 rows=36 width=32)"
"        ->  Seq Scan on measurement  (cost=0.00..25.38 rows=6 width=32)"
"              Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"        ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..25.38
rows=6 width=32)"
"              Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"        ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..25.38
rows=6 width=32)"
"              Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"        ->  Seq Scan on measurement_y2005m11 measurement  (cost=0.00..25.38
rows=6 width=32)"
"              Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"        ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..25.38
rows=6 width=32)"
"              Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"        ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..25.38
rows=6 width=32)"
"              Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"

-- Test SELECT in column type timestamp whit cast to DATE
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdatet::date = '2006-01-01'::date
"Result  (cost=0.00..170.70 rows=36 width=32)"
"  ->  Append  (cost=0.00..170.70 rows=36 width=32)"
"        ->  Seq Scan on measurement  (cost=0.00..28.45 rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2005m11 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"

SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE CAST(logdatet AS DATE) =
CAST('2006-01-01' AS DATE)
"Result  (cost=0.00..170.70 rows=36 width=32)"
"  ->  Append  (cost=0.00..170.70 rows=36 width=32)"
"        ->  Seq Scan on measurement  (cost=0.00..28.45 rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2005m11 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"
"        ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..28.45
rows=6 width=32)"
"              Filter: ((logdatet)::date = '2006-01-01'::date)"

We have similar results for timestamptz data type

Why dosen't work?

"Christian Gonzalez" <christian.gonzalez@sigis.com.ve> writes:
> Description:        Partitioning has problem with timestamp and timestamptz
> data types

In your example as given, you partition the table on "logdate", and then
add an unrelated column "logdatet" and seem to expect that the system
will think that's a partitioning condition.

I think you probably misstated your example, and meant to complain that
a table properly partitioned on a timestamptz column isn't behaving as
you'd wish for comparisons against "date" values.  The problem here is
that the conversion is dependent on TimeZone, so is not immutable, so
the planner daren't depend on it to prove that the unwanted partition
tables need not be searched.  You need to think harder about how
timezones should enter into your search rules...

            regards, tom lane