pg_part extension

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

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

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() 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() 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() 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() 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=# 

Satoshi Nagayasu <[email protected]>