Auto-partitioning in COPY

History

Auto-partitioning in COPY was a proposed feature developed by Aster Data against the PostgreSQL 9.0 code base. It was submitted and reviewed (1 2) but not accepted into the core product for that or any other version so far.

Architecture

The automatic hierarchy loading code was integrated into the code of the COPY command of Postgres 9.0 (which was still numbered 8.5 at the time). It can be enabled by adding an option to the COPY command as follows:

COPY parent_table FROM file (PARTITIONING);

We assume that the COPY is performed on the parent table and that child constraints are properly defined (no overlapping constraints). If PARTITIONING is enabled and the table has children, we scan through the list of children and check for each child table if the tuple satisfies the constraints using the Postgres internal ExecRelCheck method (we just modified the code to export that method). The tuple is moved in the first matching child table using heap_insert, indices are updated and triggers are executed. If the tuple does not match any child table constraint, an error is generated.

The error handling mechanism defines how errors are handled during COPY (see Error logging in COPY for more details).

Detailed Design

The routing code replaces the standard insert code of the COPY command in copy.c @ void CopyFrom(CopyState cstate). It integrates within the error logging second TRY/CATCH block to handle tuples that would violate all child table constraints.

The static bool route_tuple_to_child(Relation parent_relation, HeapTuple tuple) method routes a tuple into the first child table whose constraints are satisfied. We scan through the InheritsRelationId to find the child tables and try them one by one with the help of the bool check_constraints_and_insert_tuple(Relation child_table_relation, HeapTuple tuple) method. The constraints are checked using the PostgreSQL internal ExecRelCheck method that returns true if the constraints are satisfied. If this is the right child table, we execute the BEFORE ROW triggers, insert the tuple using heap_insert, update the indices and fire the AFTER ROW triggers.

Note that if any of the triggers modify the value of the key used for routing, the tuple will remain in the wrong child table leading to unpredictable behavior on further access.

As an optimization, the route_tuple_to_child method keeps an LRU sorted list of the last child table oids used. This cached table is systematically tried upfront which prevents any child table lookup if consecutive tuples have to go in the same child table (which is a likely scenario in loading). If this is a miss, a new child table lookup is performed.

We modify the GUC part of PostgreSQL to enable additional session level parameters. We are adding the following configuration options:

   * copy_partitioning_cache_size: 0 is disable, any value greater than 0 defines how many child tables oids are kept in the LRU cache

Assumptions, Limitations and Error outputs

The following cases will generate an error:

• a child table does not have any constraint defined
• tuple does not satisfy any child table constraint
• ROW and STATEMENT triggers that modify the tuple value after routing has been performed will lead to unpredictable errors.

If child tables have overlapping, the tuple will be inserted in the first child table found (be it the cached table or the first table to appear in the lookup).

Examples and Unit Tests

Here is an example used as a regression test showing the functionality. We first create a master table with 3 child tables:

	CREATE TABLE y2008 (
	  id     int not null,
	  date   date not null,
	  value  int
	);
	 
	CREATE TABLE jan2008 (
	    CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-02-01' )
	) INHERITS (y2008);
	
	CREATE TABLE feb2008 (
	    CHECK ( date >= DATE '2008-02-01' AND date < DATE '2008-03-01' )
	) INHERITS (y2008);
	
	CREATE TABLE mar2008 (
	    CHECK ( date >= DATE '2008-03-01' AND date < DATE '2008-04-01' )
	) INHERITS (y2008);

We copy the following data (1 row for each child table) in the master table:

copy_input.data content:
11	'2008-01-10'	11
12	'2008-02-15'	12
13	'2008-03-15'	13
21	'2008-01-10'	11
31	'2008-01-10'	11
41	'2008-01-10'	11
22	'2008-02-15'	12
23	'2008-03-15'	13
32	'2008-02-15'	12
33	'2008-03-15'	13
42	'2008-02-15'	12
43	'2008-03-15'	13 

Let's try a COPY without partitioning enabled:

COPY y2008 FROM '/root/workspace/Postgres-8.3.6-Aster/src/test/regress/data/copy_input.data';

All rows are inserted in the master table.

SELECT COUNT(*) FROM y2008;
 count 
-------
    12
(1 row)

SELECT COUNT(*) FROM jan2008;
 count 
-------
     0
(1 row)

SELECT COUNT(*) FROM feb2008;
 count 
-------
     0
(1 row)

SELECT COUNT(*) FROM mar2008;
 count 
-------
     0
(1 row)

DELETE FROM y2008;

We now enable automatic hierarchy loading and rows are loaded in the appropriate child table automatically.

	COPY y2008 FROM '/root/workspace/Postgres-8.5-Aster/src/test/regress/data/copy_input.data' (PARTITIONING);
	
SELECT * FROM y2008;
 id |    date    | value 
----+------------+-------
 11 | 01-10-2008 |    11
 21 | 01-10-2008 |    11
 31 | 01-10-2008 |    11
 41 | 01-10-2008 |    11
 12 | 02-15-2008 |    12
 22 | 02-15-2008 |    12
 32 | 02-15-2008 |    12
 42 | 02-15-2008 |    12
 13 | 03-15-2008 |    13
 23 | 03-15-2008 |    13
 33 | 03-15-2008 |    13
 43 | 03-15-2008 |    13
(12 rows) 

SELECT * FROM jan2008;
 id |    date    | value 
----+------------+-------
 11 | 01-10-2008 |    11
 21 | 01-10-2008 |    11
 31 | 01-10-2008 |    11
 41 | 01-10-2008 |    11
(4 rows)  

SELECT * FROM feb2008;
 id |    date    | value 
----+------------+-------
 12 | 02-15-2008 |    12
 22 | 02-15-2008 |    12
 32 | 02-15-2008 |    12
 42 | 02-15-2008 |    12
(4 rows)

SELECT * FROM mar2008;
 id |    date    | value 
----+------------+-------
 13 | 03-15-2008 |    13
 23 | 03-15-2008 |    13
 33 | 03-15-2008 |    13
 43 | 03-15-2008 |    13
(4 rows) 

The cache size can be tuned using:

set copy_partitioning_cache_size = 3;

Repeating the COPY command will now be faster:

	COPY y2008 FROM '/root/workspace/Postgres-8.5-Aster/src/test/regress/data/copy_input.data' (PARTITIONING);

Finally we drop the tables.

DROP TABLE y2008 CASCADE;
NOTICE:  drop cascades to table mar2008
NOTICE:  drop cascades to constraint mar2008_date_check on table mar2008
NOTICE:  drop cascades to table feb2008
NOTICE:  drop cascades to constraint feb2008_date_check on table feb2008
NOTICE:  drop cascades to table jan2008
NOTICE:  drop cascades to constraint jan2008_date_check on table jan2008