Skip to content
PostgreSQL Table Partitioning Utility
Latest commit 6a5c96d Jan 26, 2013 @snaga snaga - Fixed creating a new index name to be built on the new partition. (#1)
  The pg_part function attempts to create an index name, for the new
  partition to be built, from `indexdef' of the parent table.

  However, the initial pg_part release only supported simple indexes,
  built on the single column and no function.

  This fix is intended to accept multi-column indexes and function
  indexes for a new index name on the partition.

  Reported by pedroaxl.

README.md

pg_part extension

About pg_part

pg_part is a PostgreSQL extension, which provides SQL utility functions, to allow users manipulating partitioned-tables without executing PostgreSQL DDL commands directly.

SQL Functions

pg_part extension provies five SQL functions in pgpart schema, and these functions are NOT relocatable so far.

pgpart.add_partition

pgpart.add_partition() function creates new partition, which has specified check condition, from the parent table, and moves records from the parent table to the partition.

pgpart.add_partition(schema_name, table_name, partition_name, check_condition, temp_file)

Parameters:

  • schema_name : a schema name which has the table to be parted.
  • table_name : a table name to be parted.
  • partition_name : a partition name to be created.
  • check_condition : a check condition which the partition should have.
  • temp_file : a temp file name to be used for migrating records (exporting/importing) between the table and the partition.

When pgpart.add_partition() function is called, it processes followings:

  1. Create a child table, as a partition, inherited from the parent table with a check constraint.
  2. Export records to be moved from the parent table to the partition.
  3. Delete those (live) records from the parent table.
  4. Import (exported) records into the partition.
  5. Add a primary key to the partition (copied from the parent table).
  6. Add indexes to the partition (copied from the parent table).

Example:

dbt3=# SELECT pgpart.add_partition(
dbt3(#   'public',
dbt3(#   'orders',
dbt3(#   'orders_1998',
dbt3(#   ' ''1998-01-01'' <= o_orderdate AND o_orderdate < ''1999-01-01'' ',
dbt3(#   '/tmp/orders.tmp');
 add_partition
---------------
 t
(1 row)

dbt3=#

pgpart.merge_partition

pgpart.merge_partition() function merges a partition to the parent table with moving records in the partition.

pgpart.merge_partition(schema_name, table_name, partition_name, check_constraint, temp_file)

Parameters:

  • schema_name : a schema name which has the table to be merged.
  • table_name : a table name to be merged.
  • partition_name : a partition name to be merged.
  • check_condition : a check condition which the partition has. (unused)
  • temp_file : a temp file name to be used for migrating records (exporting/importing) between the table and the partition.

Example:

dbt3=# SELECT pgpart.merge_partition('public', 'orders', 'orders_1998', null, '/tmp/orders.tmp');
 merge_partition
-----------------
 t
(1 row)

dbt3=# 

pgpart.attach_partition

pgpart.attach_partition() function allows to attach a child table to the parent table as a partition when both have the same table definition.

pgpart.attach_partition(schema_name, table_name, partition_name, check_condition)

Parameters:

  • schema_name : a schema name which contains the table.
  • table_name : a table name which the partition would be attached to.
  • partition_name : a partition name to be attached.
  • check_condition : a check condition which the partition should have.

Example:

dbt3=# SELECT pgpart.attach_partition(
dbt3(#   'public',
dbt3(#   'orders',
dbt3(#   'orders_1998',
dbt3(#   ' ''1998-01-01'' <= o_orderdate AND o_orderdate < ''1999-01-01'' ');
 attach_partition
------------------
 t
(1 row)

dbt3=# 

pgpart.detach_partition

pgpart.detach_partition() function allows to detach a partition from the specified user table.

pgpart.detach_partition(schema_name, table_name, partition_name)

Parameters:

  • schema_name : a schema name which contains the table.
  • table_name : a table name which has a partition to be detached.
  • partition_name : a partition name to be detached.

Example:

dbt3=# SELECT pgpart.detach_partition('public', 'orders', 'orders_1998');
 detach_partition
------------------
 t
(1 row)

dbt3=# 

pgpart.show_partition

pgpart.show_partition() function lists partition name(s) which the specified table has.

pgpart.show_partition(schema_name, table_name)

Parameters:

  • schema_name : a schema name which contains the table.
  • table_name : a table name which has its partition(s).

Example:

dbt3=# SELECT pgpart.show_partition('public', 'orders');
 show_partition
----------------
 orders_1995
 orders_1996
 orders_1997
 orders_1998
(4 rows)

dbt3=# 

Author

Satoshi Nagayasu <snaga@uptime.jp>
Something went wrong with that request. Please try again.