Skip site navigation (1) Skip section navigation (2)

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

From: "Christian Gonzalez" <christian(dot)gonzalez(at)sigis(dot)com(dot)ve>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3235: Partitioning has problem with timestamp and timestamptz data types
Date: 2007-04-17 22:43:16
Message-ID: 200704172243.l3HMhGt8087696@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3235
Logged by:          Christian Gonzalez
Email address:      christian(dot)gonzalez(at)sigis(dot)com(dot)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?

Responses

pgsql-bugs by date

Next:From: Christian GonzalezDate: 2007-04-17 22:57:12
Subject: BUG #3236: Partitioning has problem with timestamp and timestamptz data type
Previous:From: David FetterDate: 2007-04-17 19:09:35
Subject: Re: BUG #3228: Linux/M32R project

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group