CODEWITHSUNDEEP
sqlddldatabase-partitioninggreenplum
Piyush Dugar
Piyush Dugar

Reputation: 53

Multi-level partition in Greeplum

I want to make a multilevel partition table in Greeplum with first level partition on year and second on every 7 days. When I hardcode it works fine.Following is the code :

DROP TABLE if exists pd.is_it_working;

CREATE TABLE pd.is_it_working
(
  ts timestamp without time zone,
  client_uname text
)
WITH (APPENDONLY=true, 
  OIDS=FALSE
)
DISTRIBUTED BY (client_uname)
PARTITION BY RANGE(ts)

        SUBPARTITION BY RANGE (ts)
        SUBPARTITION TEMPLATE 
        (
        START ('2015-06-01 00:00:00'::timestamp without time zone)INCLUSIVE 
        END ('2016-01-01 00:00:00'::timestamp without time zone) EXCLUSIVE
        EVERY ('1 week'::interval) 
        WITH (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608), 
        DEFAULT SUBPARTITION outlying_week  
        WITH (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608)
        )

(
START ('2015-06-01 00:00:00'::timestamp without time zone)INCLUSIVE 
END ('2016-01-01 00:00:00'::timestamp without time zone) EXCLUSIVE
EVERY ('1 year'::interval) 
WITH (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608), 
DEFAULT PARTITION outlying_year  
WITH (appendonly=true, orientation=parquet, compresstype=snappy, pagesize=1048576, rowgroupsize=8388608)
)
;
ALTER TABLE pd.is_it_working
  OWNER TO pdugar;

Now, the problem is how do I add more partitions for years(for example partition for year 2016-2017) with their subsequent 7 days partitions? I can't add partitions if I have a default partition. If I decide to split the default partition, it should not be empty otherwise I cannot partition it. Also even if it's not empty, if I split it, it will follow the subpartition template as defined above which is wrong as for a new year, a new subpartition template is supposed to be there i.e let's say for year 2016-2017 subpartition has to be every 7 day in between them !

I have to dynamically make a new year partition(with its subsequent subpartitions) into this table when the data comes in and I am not able to do it. Also I need partition to be done on 'time stamp'. Is there any way out ?

Thank You

Upvotes: 2

Views: 869

Answers (1)

Brendan Stephens
Brendan Stephens

Reputation: 227

You can't have a "default" partition and add new ones -- since the default may contain data that should be on the new partitions.

In this case, queries that contained those tuples would be missing data as the query planner uses partition elimination to make the query faster.

In order to have the functionality, you would need to create a function that would:

  • Copy the default partition to a new table
  • Drop the default partition
  • Alter table, Adding the new partitions
  • Insert Into from your Default Copy
  • Alter table, Adding a default partition (if desired)

Ideally, you would not to use a default partition, but rather use a trigger to create a new partition based on the date range when necessary. However, greenplum doesn't allow any database altering statements to run on the segments -- so, function it is!

Take a look at this for an example:

https://dba.stackexchange.com/questions/126171/how-can-i-add-multiple-partitions-to-a-greenplum-table-using-dynamic-sql

Upvotes: 1

Related Questions