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

Auto creation of Partitions

From: NikhilS <nikkhils(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Auto creation of Partitions
Date: 2007-03-06 11:57:50
Message-ID: d3c4af540703060357i2e69828ah772bbba0e54f1e2b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Hi,

This is to get feedback to meet the following TODO:

   - Simplify ability to create partitioned tables

   This would allow creation of partitioned tables without requiring
   creation of rules for INSERT/UPDATE/DELETE, and constraints for rapid
   partition selection. Options could include range and hash partition
   selection.


There was some discussion on the pgsql mailing lists, which lead to the
above TODO:
http://archives.postgresql.org/pgsql-hackers/2006-09/msg00189.php
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01874.php

We can have the following syntax to support auto creation of partitions in
Postgresql:

 CREATE TABLE tabname (
     ...
     ...
  ) PARTITION BY
  HASH(expr)
 | RANGE(expr)
 | LIST(expr)
 [PARTITIONS num_partitions] /* will apply to HASH only for now*/
 [PARTITION partition_name CHECK(...),
  PARTITION partition_name CHECK(...)
  ...
 ];

Here "expr" will be one of the column names as specified for the master
table. Once we finalize the syntax, the above statement would end up
carrying out the following activities (disclaimer: we might add or remove
some activities based on the discussion here).

i ) Create master table.
ii) Create children tables based on the number of partitions specified and
make them inherit from the master table.
iii) Auto generate rules (or triggers?) using the checks mentioned for the
partitions, to handle INSERTs/DELETEs/UPDATEs to navigate them to the
appropriate child. Note that checks specified directly on the master table
will get inherited automatically.
iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified, pass it
on to the children tables.
v) If possible add CHECK (false) to the master table to avoid any activity
on it.

 Some questions remain as to:

1) Whether we should use triggers/rules for step number (iii) above. Maybe
rules is the way to go.
2) What other attributes (access permissions e.g.) of the master along with
the ones specified in (iv) should be passed on to the children.
3) Some implementation specific issue e.g. whether SPI_execute would be a
good way of creating these rules.

Comments appreciated,
Regards,
Nikhils
EnterpriseDB               http://www.enterprisedb.com

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2007-03-06 12:02:19
Subject: Re: Latest plans for Utilities with HOT
Previous:From: Martijn van OosterhoutDate: 2007-03-06 11:36:40
Subject: Re: user-defined tree methods in GIST

pgsql-patches by date

Next:From: Peter EisentrautDate: 2007-03-06 12:40:02
Subject: Re: Auto creation of Partitions
Previous:From: Gregory StarkDate: 2007-03-06 11:12:47
Subject: Re: Aggressive freezing in lazy-vacuum

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