0002-hash-partitioning_another_design-v12.patch
application/octet-stream
Filename: 0002-hash-partitioning_another_design-v12.patch
Type: application/octet-stream
Part: 0
Message:
Re: [POC] hash partitioning
Patch
Format: format-patch
Series: patch v12-0002
Subject: hash-partitioning_another_design-v12
| File | + | − |
|---|---|---|
| doc/src/sgml/ddl.sgml | 21 | 8 |
| doc/src/sgml/ref/alter_table.sgml | 7 | 0 |
| doc/src/sgml/ref/create_table.sgml | 62 | 8 |
| src/backend/catalog/partition.c | 526 | 43 |
| src/backend/commands/tablecmds.c | 29 | 11 |
| src/backend/nodes/copyfuncs.c | 2 | 0 |
| src/backend/nodes/equalfuncs.c | 2 | 0 |
| src/backend/nodes/outfuncs.c | 2 | 0 |
| src/backend/nodes/readfuncs.c | 2 | 0 |
| src/backend/parser/gram.y | 73 | 2 |
| src/backend/parser/parse_utilcmd.c | 24 | 1 |
| src/backend/utils/adt/ruleutils.c | 13 | 0 |
| src/bin/psql/tab-complete.c | 1 | 1 |
| src/include/catalog/pg_proc.h | 4 | 0 |
| src/include/nodes/parsenodes.h | 7 | 1 |
| src/test/regress/expected/alter_table.out | 64 | 2 |
| src/test/regress/expected/create_table.out | 62 | 9 |
| src/test/regress/expected/insert.out | 46 | 0 |
| src/test/regress/expected/update.out | 29 | 0 |
| src/test/regress/sql/alter_table.sql | 66 | 2 |
| src/test/regress/sql/create_table.sql | 39 | 5 |
| src/test/regress/sql/insert.sql | 33 | 0 |
| src/test/regress/sql/update.sql | 28 | 0 |
From 10caae1db2a05fde0ea058917fe067045c05f518 Mon Sep 17 00:00:00 2001
From: Amul Sul <sulamul@gmail.com>
Date: Fri, 19 May 2017 14:19:46 +0530
Subject: [PATCH 2/2] hash-partitioning_another_design-v12
v12:
document update
v11:
Changes w.r.t Robert's review comments in message-id :
CA%2BTgmoabcyyYPe_TRiHyXb%2BAa2rQ%2BzVC9ZHHLASPHmmYbp4sig%40mail.gmail.com
---
doc/src/sgml/ddl.sgml | 29 +-
doc/src/sgml/ref/alter_table.sgml | 7 +
doc/src/sgml/ref/create_table.sgml | 70 +++-
src/backend/catalog/partition.c | 569 ++++++++++++++++++++++++++---
src/backend/commands/tablecmds.c | 40 +-
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/outfuncs.c | 2 +
src/backend/nodes/readfuncs.c | 2 +
src/backend/parser/gram.y | 75 +++-
src/backend/parser/parse_utilcmd.c | 25 +-
src/backend/utils/adt/ruleutils.c | 13 +
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_proc.h | 4 +
src/include/nodes/parsenodes.h | 8 +-
src/test/regress/expected/alter_table.out | 66 +++-
src/test/regress/expected/create_table.out | 71 +++-
src/test/regress/expected/insert.out | 46 +++
src/test/regress/expected/update.out | 29 ++
src/test/regress/sql/alter_table.sql | 68 +++-
src/test/regress/sql/create_table.sql | 44 ++-
src/test/regress/sql/insert.sql | 33 ++
src/test/regress/sql/update.sql | 28 ++
23 files changed, 1142 insertions(+), 93 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 84c4f20..5bc1a1b 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2852,6 +2852,20 @@ VALUES ('Albany', NULL, NULL, 'NY');
<variablelist>
<varlistentry>
+ <term>Hash Partitioning</term>
+
+ <listitem>
+ <para>
+ The table is partitioned by specifying modulus and remainder for each
+ partition. Each partition holds rows for which the hash value of
+ partition keys when divided by specified modulus produces specified
+ remainder. For more clarification on modulus and remainder please refer
+ <xref linkend="sql-createtable-partition">.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term>Range Partitioning</term>
<listitem>
@@ -2901,9 +2915,8 @@ VALUES ('Albany', NULL, NULL, 'NY');
All rows inserted into a partitioned table will be routed to one of the
<firstterm>partitions</firstterm> based on the value of the partition
key. Each partition has a subset of the data defined by its
- <firstterm>partition bounds</firstterm>. Currently supported
- partitioning methods include range and list, where each partition is
- assigned a range of keys and a list of keys, respectively.
+ <firstterm>partition bounds</firstterm>. The currently supported
+ partitioning methods are list, range, and hash.
</para>
<para>
@@ -3327,11 +3340,11 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
<listitem>
<para>
- Declarative partitioning only supports list and range partitioning,
- whereas table inheritance allows data to be divided in a manner of
- the user's choosing. (Note, however, that if constraint exclusion is
- unable to prune partitions effectively, query performance will be very
- poor.)
+ Declarative partitioning only supports hash, list and range
+ partitioning, whereas table inheritance allows data to be divided in a
+ manner of the user's choosing. (Note, however, that if constraint
+ exclusion is unable to prune partitions effectively, query performance
+ will be very poor.)
</para>
</listitem>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 56ea830..0468770 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1396,6 +1396,13 @@ ALTER TABLE cities
</programlisting></para>
<para>
+ Attach a partition to hash partitioned table:
+<programlisting>
+ALTER TABLE orders
+ ATTACH PARTITION orders_p4 FOR VALUES WITH (modulus 4, remainder 3);
+</programlisting></para>
+
+ <para>
Detach a partition from partitioned table:
<programlisting>
ALTER TABLE cities
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 0478e40..0fff4dd 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -28,7 +28,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
-[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
+[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
@@ -39,7 +39,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
| <replaceable>table_constraint</replaceable> }
[, ... ]
) ]
-[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
+[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
@@ -50,7 +50,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
| <replaceable>table_constraint</replaceable> }
[, ... ]
) ] FOR VALUES <replaceable class="PARAMETER">partition_bound_spec</replaceable>
-[ PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
+[ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
@@ -87,7 +87,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase>
{ IN ( { <replaceable class="PARAMETER">bound_literal</replaceable> | NULL } [, ...] ) |
- FROM ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) }
+ FROM ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) TO ( { <replaceable class="PARAMETER">bound_literal</replaceable> | UNBOUNDED } [, ...] ) |
+ WITH ( MODULUS <replaceable class="PARAMETER">modulus</replaceable>, REMAINDER <replaceable class="PARAMETER">remainder</replaceable> ) }
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
@@ -301,6 +302,29 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</para>
<para>
+ When creating a hash partition, a modulus and remainder must be specified.
+ The modulus must be a positive integer, and the remainder must a
+ non-negative integer less than the modulus. Typically, when initially
+ setting up a hash-partitioned table, you should choose a modulus equal to
+ the number of partitions and assign every table the same modulus and a
+ different remainder (see examples, below). However, it is not required
+ that every partition have the same modulus, only that every modulus which
+ occurs among the children of a hash-partitioned table is a factor of the
+ next larger modulus. This allows the number of partitions to be increased
+ incrementally without needing to move all the data at once. For example,
+ suppose you have a hash-partitioned table with 8 children, each of which
+ has modulus 8, but find it necessary to increase the number of partitions
+ to 16. You can detach one of the modulus-8 partitions, create two new
+ modulus-16 partitions covering the same portion of the key space (one with
+ a remainder equal to the remainder of the detached partition, and the
+ other with a remainder equal to that value plus 8), and repopulate them
+ with data. You can then repeat this -- perhaps at a later time -- for
+ each modulus-8 partition until none remain. While this may still involve
+ a large amount of data movement at each step, it is still better than
+ having to create a whole new table and move all the data at once.
+ </para>
+
+ <para>
A partition must have the same column names and types as the partitioned
table to which it belongs. If the parent is specified <literal>WITH
OIDS</literal> then all partitions must have OIDs; the parent's OID
@@ -422,7 +446,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
- <term><literal>PARTITION BY { RANGE | LIST } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) </literal></term>
+ <term><literal>PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) </literal></term>
<listitem>
<para>
The optional <literal>PARTITION BY</literal> clause specifies a strategy
@@ -433,9 +457,16 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
include multiple columns or expressions (up to 32, but this limit can
altered when building <productname>PostgreSQL</productname>.), but for
list partitioning, the partition key must consist of a single column or
- expression. If no btree operator class is specified when creating a
- partitioned table, the default btree operator class for the datatype will
- be used. If there is none, an error will be reported.
+ expression. If no operator class is specified when creating a partitioned
+ table, the default operator class of the appropriate type (btree for list
+ and range partitioning, hash for hash partitioning) will be used. If
+ there is none, an error will be reported.
+ </para>
+
+ <para>
+ Since hash partitioning operator class provide only equality, not
+ ordering, collation is not relevant for hash partitioning. The behaviour
+ will be unaffected if a collation is specified.
</para>
<para>
@@ -1636,6 +1667,29 @@ CREATE TABLE cities_ab
CREATE TABLE cities_ab_10000_to_100000
PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
</programlisting></para>
+
+ <para>
+ Create a hash partitioned table:
+<programlisting>
+CREATE TABLE orders (
+ order_id bigint not null,
+ cust_id bigint not null,
+ status text
+) PARTITION BY HASH (order_id);
+</programlisting></para>
+
+ <para>
+ Create partitions of a hash partitioned table:
+<programlisting>
+CREATE TABLE orders_p1 PARTITION OF orders
+ FOR VALUES WITH(MODULUS 4, REMAINDER 0);
+CREATE TABLE orders_p2 PARTITION OF orders
+ FOR VALUES WITH(MODULUS 4, REMAINDER 1);
+CREATE TABLE orders_p3 PARTITION OF orders
+ FOR VALUES WITH(MODULUS 4, REMAINDER 2);
+CREATE TABLE orders_p4 PARTITION OF orders
+ FOR VALUES WITH(MODULUS 4, REMAINDER 3);
+</programlisting></para>
</refsect1>
<refsect1 id="SQL-CREATETABLE-compatibility">
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index 763a24b..792dd6d 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -58,12 +58,24 @@
* In the case of range partitioning, ndatums will typically be far less than
* 2 * nparts, because a partition's upper bound and the next partition's lower
* bound are the same in most common cases, and we only store one of them.
+ * In case of hash partitioning, ndatums will be same as the number of
+ * partitions.
+ *
+ * For range and list partitioned tables, datums is an array of datum-tuples
+ * with key->partnatts datums each. For hash partitioned tables, it is an array
+ * of datum-tuples with 2 datums, modulus and remainder, corresponding to a
+ * given partition.
*
* In the case of list partitioning, the indexes array stores one entry for
* every datum, which is the index of the partition that accepts a given datum.
* In case of range partitioning, it stores one entry per distinct range
* datum, which is the index of the partition for which a given datum
* is an upper bound.
+ * In the case of hash partitioning, the number of the entries in the indexes
+ * array is same as the greatest modulus amongst all partitions. For a given
+ * partition key datum-tuple, the index of the partition which would accept that
+ * datum-tuple would be given by the entry pointed by remainder produced when
+ * hash value of the datum-tuple is divided by the greatest modulus.
*/
/* Ternary value to represent what's contained in a range bound datum */
@@ -76,18 +88,16 @@ typedef enum RangeDatumContent
typedef struct PartitionBoundInfoData
{
- char strategy; /* list or range bounds? */
+ char strategy; /* hash, list or range? */
int ndatums; /* Length of the datums following array */
- Datum **datums; /* Array of datum-tuples with key->partnatts
- * datums each */
+ Datum **datums;
RangeDatumContent **content;/* what's contained in each range bound datum?
- * (see the above enum); NULL for list
- * partitioned tables */
- int *indexes; /* Partition indexes; one entry per member of
- * the datums array (plus one if range
- * partitioned table) */
+ * (see the above enum); NULL for hash and
+ * list partitioned tables */
+ int *indexes; /* Partition indexes */
int null_index; /* Index of the null-accepting partition; -1
- * if there isn't one */
+ * if there isn't one, or is a hash or range
+ * partitioned table */
} PartitionBoundInfoData;
#define partition_bound_accepts_nulls(bi) ((bi)->null_index != -1)
@@ -97,6 +107,14 @@ typedef struct PartitionBoundInfoData
* is represented with one of the following structs.
*/
+/* One bound of a hash partition */
+typedef struct PartitionHashBound
+{
+ int modulus;
+ int remainder;
+ int index;
+} PartitionHashBound;
+
/* One value coming from some (index'th) list partition */
typedef struct PartitionListValue
{
@@ -113,6 +131,7 @@ typedef struct PartitionRangeBound
bool lower; /* this is the lower (vs upper) bound */
} PartitionRangeBound;
+static int32 qsort_partition_hbound_cmp(const void *a, const void *b);
static int32 qsort_partition_list_value_cmp(const void *a, const void *b,
void *arg);
static int32 qsort_partition_rbound_cmp(const void *a, const void *b,
@@ -128,12 +147,15 @@ static void get_range_key_properties(PartitionKey key, int keynum,
ListCell **partexprs_item,
Expr **keyCol,
Const **lower_val, Const **upper_val);
+static List *get_qual_for_hash(PartitionKey key, PartitionBoundSpec *spec);
static List *get_qual_for_list(PartitionKey key, PartitionBoundSpec *spec);
static List *get_qual_for_range(PartitionKey key, PartitionBoundSpec *spec);
static List *generate_partition_qual(Relation rel);
static PartitionRangeBound *make_one_range_bound(PartitionKey key, int index,
List *datums, bool lower);
+static int32 partition_hbound_cmp(int modulus1, int remainder1, int modulus2,
+ int remainder2);
static int32 partition_rbound_cmp(PartitionKey key,
Datum *datums1, RangeDatumContent *content1, bool lower1,
PartitionRangeBound *b2);
@@ -147,6 +169,10 @@ static int32 partition_bound_cmp(PartitionKey key,
static int partition_bound_bsearch(PartitionKey key,
PartitionBoundInfo boundinfo,
void *probe, bool probe_is_bound, bool *is_equal);
+static uint32 compute_hash_value(PartitionKey key, Datum *values, bool *isnull);
+
+/* SQL-callable function for use in hash partition CHECK constraints */
+PG_FUNCTION_INFO_V1(satisfies_hash_partition);
/*
* RelationBuildPartitionDesc
@@ -171,6 +197,9 @@ RelationBuildPartitionDesc(Relation rel)
int ndatums = 0;
+ /* Hash partitioning specific */
+ PartitionHashBound **hbounds = NULL;
+
/* List partitioning specific */
PartitionListValue **all_values = NULL;
int null_index = -1;
@@ -236,7 +265,33 @@ RelationBuildPartitionDesc(Relation rel)
oids[i++] = lfirst_oid(cell);
/* Convert from node to the internal representation */
- if (key->strategy == PARTITION_STRATEGY_LIST)
+ if (key->strategy == PARTITION_STRATEGY_HASH)
+ {
+ ndatums = nparts;
+ hbounds = (PartitionHashBound **) palloc(nparts *
+ sizeof(PartitionHashBound *));
+ i = 0;
+ foreach(cell, boundspecs)
+ {
+ PartitionBoundSpec *spec = lfirst(cell);
+
+ if (spec->strategy != PARTITION_STRATEGY_HASH)
+ elog(ERROR, "invalid strategy in partition bound spec");
+
+ hbounds[i] = (PartitionHashBound *)
+ palloc(sizeof(PartitionHashBound));
+
+ hbounds[i]->modulus = spec->modulus;
+ hbounds[i]->remainder = spec->remainder;
+ hbounds[i]->index = i;
+ i++;
+ }
+
+ /* Sort all the bounds in ascending order */
+ qsort(hbounds, nparts, sizeof(PartitionHashBound *),
+ qsort_partition_hbound_cmp);
+ }
+ else if (key->strategy == PARTITION_STRATEGY_LIST)
{
List *non_null_values = NIL;
@@ -461,6 +516,42 @@ RelationBuildPartitionDesc(Relation rel)
switch (key->strategy)
{
+ case PARTITION_STRATEGY_HASH:
+ {
+ /* Modulus are stored in ascending order */
+ int greatest_modulus = hbounds[ndatums - 1]->modulus;
+
+ boundinfo->indexes = (int *) palloc(greatest_modulus *
+ sizeof(int));
+
+ for (i = 0; i < greatest_modulus; i++)
+ boundinfo->indexes[i] = -1;
+
+ for (i = 0; i < nparts; i++)
+ {
+ int modulus = hbounds[i]->modulus;
+ int remainder = hbounds[i]->remainder;
+
+ boundinfo->datums[i] = (Datum *) palloc(2 *
+ sizeof(Datum));
+ boundinfo->datums[i][0] = Int32GetDatum(modulus);
+ boundinfo->datums[i][1] = Int32GetDatum(remainder);
+
+ while (remainder < greatest_modulus)
+ {
+ /* overlap? */
+ Assert(boundinfo->indexes[remainder] == -1);
+ boundinfo->indexes[remainder] = i;
+ remainder += modulus;
+ }
+
+ mapping[hbounds[i]->index] = i;
+ pfree(hbounds[i]);
+ }
+ pfree(hbounds);
+ break;
+ }
+
case PARTITION_STRATEGY_LIST:
{
boundinfo->indexes = (int *) palloc(ndatums * sizeof(int));
@@ -608,53 +699,106 @@ partition_bounds_equal(PartitionKey key,
if (b1->null_index != b2->null_index)
return false;
- for (i = 0; i < b1->ndatums; i++)
+ if (key->strategy == PARTITION_STRATEGY_HASH)
{
- int j;
+ int greatest_modulus;
+
+ /*
+ * If two hash partitioned tables have different greatest moduli or
+ * same moduli with different number of partitions, their partition
+ * schemes don't match. For hash partitioned table, the greatest
+ * modulus is given by the last datum and number of partitions is
+ * given by ndatums.
+ */
+ if (b1->datums[b1->ndatums - 1][0] != b2->datums[b2->ndatums - 1][0])
+ return false;
- for (j = 0; j < key->partnatts; j++)
+ if (b1->ndatums != b2->ndatums)
+ return false;
+
+ /*
+ * We arrange the partitions in the ascending order of their modulus
+ * and remainders. Also every modulus is factor of next larger
+ * modulus. This means that the index of a given partition is same as
+ * the remainder of that partition. Also entries at (remainder + N *
+ * modulus) positions in indexes array are all same for (modulus,
+ * remainder) specification for any partition. Thus datums array from
+ * both the given bounds are same, if and only if their indexes array
+ * will be same. So, it suffices to compare indexes array.
+ */
+ greatest_modulus = DatumGetInt32(b1->datums[b1->ndatums - 1][0]);
+ for (i = 0; i < greatest_modulus; i++)
+ if (b1->indexes[i] != b2->indexes[i])
+ return false;
+
+#ifdef USE_ASSERT_CHECKING
+
+ /*
+ * Nonetheless make sure that the bounds are indeed same when the
+ * indexes match. Hash partition bound stores modulus and remainder
+ * at b1->datums[i][0] and b1->datums[i][1] position respectively.
+ */
+ for (i = 0; i < b1->ndatums; i++)
+ Assert((b1->datums[i][0] == b2->datums[i][0] &&
+ b1->datums[i][1] == b2->datums[i][1]));
+#endif
+ }
+ else
+ {
+ for (i = 0; i < b1->ndatums; i++)
{
- /* For range partitions, the bounds might not be finite. */
- if (b1->content != NULL)
+ int j;
+
+ for (j = 0; j < key->partnatts; j++)
{
+ /* For range partitions, the bounds might not be finite. */
+ if (b1->content != NULL)
+ {
+ /*
+ * A finite bound always differs from an infinite bound,
+ * and different kinds of infinities differ from each
+ * other.
+ */
+ if (b1->content[i][j] != b2->content[i][j])
+ return false;
+
+ /*
+ * Non-finite bounds are equal without further
+ * examination.
+ */
+ if (b1->content[i][j] != RANGE_DATUM_FINITE)
+ continue;
+ }
+
/*
- * A finite bound always differs from an infinite bound, and
- * different kinds of infinities differ from each other.
+ * Compare the actual values. Note that it would be both
+ * incorrect and unsafe to invoke the comparison operator
+ * derived from the partitioning specification here. It would
+ * be incorrect because we want the relcache entry to be
+ * updated for ANY change to the partition bounds, not just
+ * those that the partitioning operator thinks are
+ * significant. It would be unsafe because we might reach
+ * this code in the context of an aborted transaction, and an
+ * arbitrary partitioning operator might not be safe in that
+ * context. datumIsEqual() should be simple enough to be
+ * safe.
*/
- if (b1->content[i][j] != b2->content[i][j])
+ if (!datumIsEqual(b1->datums[i][j], b2->datums[i][j],
+ key->parttypbyval[j],
+ key->parttyplen[j]))
return false;
-
- /* Non-finite bounds are equal without further examination. */
- if (b1->content[i][j] != RANGE_DATUM_FINITE)
- continue;
}
- /*
- * Compare the actual values. Note that it would be both incorrect
- * and unsafe to invoke the comparison operator derived from the
- * partitioning specification here. It would be incorrect because
- * we want the relcache entry to be updated for ANY change to the
- * partition bounds, not just those that the partitioning operator
- * thinks are significant. It would be unsafe because we might
- * reach this code in the context of an aborted transaction, and
- * an arbitrary partitioning operator might not be safe in that
- * context. datumIsEqual() should be simple enough to be safe.
- */
- if (!datumIsEqual(b1->datums[i][j], b2->datums[i][j],
- key->parttypbyval[j],
- key->parttyplen[j]))
+ if (b1->indexes[i] != b2->indexes[i])
return false;
}
- if (b1->indexes[i] != b2->indexes[i])
+ /* There are ndatums+1 indexes in case of range partitions */
+ if (key->strategy == PARTITION_STRATEGY_RANGE &&
+ b1->indexes[i] != b2->indexes[i])
return false;
}
- /* There are ndatums+1 indexes in case of range partitions */
- if (key->strategy == PARTITION_STRATEGY_RANGE &&
- b1->indexes[i] != b2->indexes[i])
- return false;
-
return true;
}
@@ -676,6 +820,89 @@ check_new_partition_bound(char *relname, Relation parent, Node *bound)
switch (key->strategy)
{
+ case PARTITION_STRATEGY_HASH:
+ {
+ Assert(spec->strategy == PARTITION_STRATEGY_HASH);
+ Assert(spec->remainder >= 0 && spec->remainder < spec->modulus);
+
+ if (partdesc->nparts > 0)
+ {
+ PartitionBoundInfo boundinfo = partdesc->boundinfo;
+ Datum **datums = boundinfo->datums;
+ int ndatums = boundinfo->ndatums;
+ int greatest_modulus;
+ int remainder;
+ int offset;
+ bool equal,
+ valid_modulus = true;
+ int prev_modulus, /* Previous largest modulus */
+ next_modulus; /* Next largest modulus */
+
+ /*
+ * Check rule that every modulus must be a factor of the
+ * next larger modulus. For example, if you have a bunch
+ * of partitions that all have modulus 5, you can add a
+ * new new partition with modulus 10 or a new partition
+ * with modulus 15, but you cannot add both a partition
+ * with modulus 10 and a partition with modulus 15,
+ * because 10 is not a factor of 15.
+ *
+ * Get greatest bound in array boundinfo->datums which is
+ * less than or equal to spec->modulus and
+ * spec->remainder.
+ */
+ offset = partition_bound_bsearch(key, boundinfo, spec,
+ true, &equal);
+ if (offset < 0)
+ {
+ next_modulus = DatumGetInt32(datums[0][0]);
+ valid_modulus = (next_modulus % spec->modulus) == 0;
+ }
+ else
+ {
+ prev_modulus = DatumGetInt32(datums[offset][0]);
+ valid_modulus = (spec->modulus % prev_modulus) == 0;
+
+ if (valid_modulus && (offset + 1) < ndatums)
+ {
+ next_modulus = DatumGetInt32(datums[offset + 1][0]);
+ valid_modulus = (next_modulus % spec->modulus) == 0;
+ }
+ }
+
+ if (!valid_modulus)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("every hash partition modulus must be a factor of the next larger modulus")));
+
+ greatest_modulus = DatumGetInt32(datums[ndatums - 1][0]);
+ remainder = spec->remainder;
+
+ /*
+ * Normally, the lowest remainder that could conflict with
+ * the new partition is equal to the remainder specified
+ * for the new partition, but when the new partition has a
+ * modulus higher than any used so far, we need to adjust.
+ */
+ if (remainder >= greatest_modulus)
+ remainder = remainder % greatest_modulus;
+
+ /* Check every potentially-conflicting remainder. */
+ do
+ {
+ if (boundinfo->indexes[remainder] != -1)
+ {
+ overlap = true;
+ with = boundinfo->indexes[remainder];
+ break;
+ }
+ remainder += spec->modulus;
+ } while (remainder < greatest_modulus);
+ }
+
+ break;
+ }
+
case PARTITION_STRATEGY_LIST:
{
Assert(spec->strategy == PARTITION_STRATEGY_LIST);
@@ -899,6 +1126,11 @@ get_qual_from_partbound(Relation rel, Relation parent, Node *bound)
switch (key->strategy)
{
+ case PARTITION_STRATEGY_HASH:
+ Assert(spec->strategy == PARTITION_STRATEGY_HASH);
+ my_qual = get_qual_for_hash(key, spec);
+ break;
+
case PARTITION_STRATEGY_LIST:
Assert(spec->strategy == PARTITION_STRATEGY_LIST);
my_qual = get_qual_for_list(key, spec);
@@ -1292,6 +1524,109 @@ make_partition_op_expr(PartitionKey key, int keynum,
}
/*
+ * get_qual_for_hash
+ *
+ * Given a list of partition columns, modulus and remainder corresponding to a
+ * partition, this function returns CHECK constraint expression Node for that
+ * partition.
+ *
+ * For a partitioned table defined as:
+ * CREATE TABLE simple_hash (a int, b char(10)) PARTITION BY HASH (a, b);
+ *
+ * CREATE TABLE p_p1 PARTITION OF simple_hash
+ * FOR VALUES WITH (modulus 2, remainder 1);
+ * CREATE TABLE p_p2 PARTITION OF simple_hash
+ * FOR VALUES WITH (modulus 4, remainder 2);
+ * CREATE TABLE p_p3 PARTITION OF simple_hash
+ * FOR VALUES WITH (modulus 8, remainder 0);
+ * CREATE TABLE p_p4 PARTITION OF simple_hash
+ * FOR VALUES WITH (modulus 8, remainder 4);
+ *
+ * This function will return one of the following in the form of an
+ * expression:
+ *
+ * for p_p1: satisfies_hash_partition(2, 1, hash_fn_1(a), hash_fn_2(b))
+ * for p_p2: satisfies_hash_partition(4, 2, hash_fn_1(a), hash_fn_2(b))
+ * for p_p3: satisfies_hash_partition(8, 0, hash_fn_1(a), hash_fn_2(b))
+ * for p_p4: satisfies_hash_partition(8, 4, hash_fn_1(a), hash_fn_2(b))
+ *
+ * where hash_fn_1 and hash_fn_2 are be datatype-specific hash functions for
+ * columns a and b respectively.
+ */
+static List *
+get_qual_for_hash(PartitionKey key, PartitionBoundSpec *spec)
+{
+ FuncExpr *fexpr;
+ Node *modulusConst;
+ Node *remainderConst;
+ List *args;
+ ListCell *partexprs_item;
+ int i;
+
+ /* Fixed arguments. */
+ modulusConst = (Node *) makeConst(INT4OID,
+ -1,
+ InvalidOid,
+ sizeof(int32),
+ Int32GetDatum(spec->modulus),
+ false,
+ true);
+
+ remainderConst = (Node *) makeConst(INT4OID,
+ -1,
+ InvalidOid,
+ sizeof(int32),
+ Int32GetDatum(spec->remainder),
+ false,
+ true);
+
+ args = list_make2(modulusConst, remainderConst);
+ partexprs_item = list_head(key->partexprs);
+
+ /* Add an argument for each key column. */
+ for (i = 0; i < key->partnatts; i++)
+ {
+ Node *keyCol;
+
+ /* Left operand */
+ if (key->partattrs[i] != 0)
+ keyCol = (Node *) makeVar(1,
+ key->partattrs[i],
+ key->parttypid[i],
+ key->parttypmod[i],
+ key->parttypcoll[i],
+ 0);
+ else
+ {
+ if (partexprs_item == NULL)
+ elog(ERROR, "wrong number of partition key expressions");
+
+ keyCol = (Node *) copyObject(lfirst(partexprs_item));
+ partexprs_item = lnext(partexprs_item);
+ }
+
+ /* Form hash_fn(keyCol) expression */
+ fexpr = makeFuncExpr(key->partsupfunc[i].fn_oid,
+ get_fn_expr_rettype(&key->partsupfunc[i]),
+ list_make1(keyCol),
+ InvalidOid,
+ InvalidOid,
+ COERCE_EXPLICIT_CALL);
+
+ args = lappend(args, fexpr);
+ }
+
+ fexpr = makeFuncExpr(F_SATISFIES_HASH_PARTITION,
+ BOOLOID,
+ args,
+ InvalidOid,
+ InvalidOid,
+ COERCE_EXPLICIT_CALL);
+
+ return list_make1(fexpr);
+}
+
+/*
* get_qual_for_list
*
* Returns a list of expressions to use as a list partition's constraint.
@@ -1969,6 +2304,19 @@ get_partition_for_tuple(PartitionDispatch *pd,
switch (key->strategy)
{
+ case PARTITION_STRATEGY_HASH:
+ {
+ PartitionBoundInfo boundinfo = partdesc->boundinfo;
+ int ndatums = boundinfo->ndatums;
+ Datum datum = boundinfo->datums[ndatums - 1][0];
+ int greatest_modulus = DatumGetInt32(datum);
+ uint32 rowHash = compute_hash_value(key, values,
+ isnull);
+
+ cur_index = boundinfo->indexes[rowHash % greatest_modulus];
+ }
+ break;
+
case PARTITION_STRATEGY_LIST:
/*
@@ -2022,7 +2370,8 @@ get_partition_for_tuple(PartitionDispatch *pd,
/* bsearch in partdesc->boundinfo */
cur_offset = partition_bound_bsearch(key,
partdesc->boundinfo,
- values, false, &equal);
+ values, false, &equal);
+
/*
* Offset returned is such that the bound at offset is
* found to be less or equal with the tuple. So, the bound
@@ -2064,6 +2413,34 @@ error_exit:
}
/*
+ * We sort hash bounds by modulus, then by remainder.
+ */
+static int32
+qsort_partition_hbound_cmp(const void *a, const void *b)
+{
+ PartitionHashBound *h1 = (*(PartitionHashBound * const *) a);
+ PartitionHashBound *h2 = (*(PartitionHashBound * const *) b);
+
+ return partition_hbound_cmp(h1->modulus, h1->remainder,
+ h2->modulus, h2->remainder);
+}
+
+/*
+ * Compares modulus first, then remainder if modulus are equal.
+ */
+static int32
+partition_hbound_cmp(int modulus1, int remainder1, int modulus2, int remainder2)
+{
+ if (modulus1 < modulus2)
+ return -1;
+ if (modulus1 > modulus2)
+ return 1;
+ if (modulus1 == modulus2 && remainder1 != remainder2)
+ return (remainder1 > remainder2) ? 1 : -1;
+ return 0;
+}
+
+/*
* qsort_partition_list_value_cmp
*
* Compare two list partition bound datums
@@ -2240,6 +2617,15 @@ partition_bound_cmp(PartitionKey key, PartitionBoundInfo boundinfo,
switch (key->strategy)
{
+ case PARTITION_STRATEGY_HASH:
+ {
+ PartitionBoundSpec *spec = (PartitionBoundSpec *) probe;
+
+ cmpval = partition_hbound_cmp(DatumGetInt32(bound_datums[0]),
+ DatumGetInt32(bound_datums[1]),
+ spec->modulus, spec->remainder);
+ break;
+ }
case PARTITION_STRATEGY_LIST:
cmpval = DatumGetInt32(FunctionCall2Coll(&key->partsupfunc[0],
key->partcollation[0],
@@ -2323,3 +2709,100 @@ partition_bound_bsearch(PartitionKey key, PartitionBoundInfo boundinfo,
return lo;
}
+
+/*
+ * This function takes an already computed hash values and combine them
+ * into a single 32-bit value.
+ */
+static uint32
+mix_hash_value(int nkeys, uint32 *hash_array, bool *isnull)
+{
+ int i;
+ uint32 rowHash = 0;
+
+ for (i = 0; i < nkeys; i++)
+ {
+ /*
+ * Like TupleHashTableHash, rotate hashkey left 1 bit at each step.
+ * This prevents equal values in different keys from cancelling each
+ * other.
+ */
+ rowHash = (rowHash << 1) | ((rowHash & 0x80000000) ? 1 : 0);
+
+ if (!isnull[i])
+ rowHash ^= hash_array[i];
+ }
+
+ return rowHash;
+}
+
+/*
+ * Compute the hash value for given not null partition key values.
+ */
+static uint32
+compute_hash_value(PartitionKey key, Datum *values, bool *isnull)
+{
+ int i;
+ int nkeys = key->partnatts;
+ uint32 hash_array[PARTITION_MAX_KEYS];
+
+ for (i = 0; i < nkeys; i++)
+ {
+ if (!isnull[i])
+ {
+ Assert(OidIsValid(key->partsupfunc[i].fn_oid));
+
+ /*
+ * Compute hash for each datum value by calling respective
+ * datatype-specific hash functions of each partition key
+ * attribute.
+ */
+ hash_array[i] = DatumGetUInt32(FunctionCall1(&key->partsupfunc[i],
+ values[i]));
+ }
+ }
+
+ /* Form a single 32-bit hash value */
+ return mix_hash_value(nkeys, hash_array, isnull);
+}
+
+/*
+ * satisfies_hash_partition
+ *
+ * This is a SQL-callable function for use in hash partition constraints takes
+ * an already computed hash values of each partition key attribute, and combine
+ * them into a single hash value by calling mix_hash_value.
+ *
+ * Returns true if remainder produced when this computed single hash value is
+ * divided by the given modulus is equal to given remainder, otherwise false.
+ *
+ * See get_qual_for_hash() for usage.
+ */
+Datum
+satisfies_hash_partition(PG_FUNCTION_ARGS)
+{
+ int modulus = PG_GETARG_INT32(0);
+ int remainder = PG_GETARG_INT32(1);
+ short nkeys = PG_NARGS() - 2;
+ int i;
+ uint32 hash_array[PARTITION_MAX_KEYS];
+ bool isnull[PARTITION_MAX_KEYS];
+ uint32 rowHash = 0;
+
+ for (i = 0; i < nkeys; i++)
+ {
+ /*
+ * Partition key attribute's hash values start from third argument of
+ * function.
+ */
+ isnull[i] = PG_ARGISNULL(i + 2);
+
+ if (!isnull[i])
+ hash_array[i] = PG_GETARG_UINT32(i + 2);
+ }
+
+ /* Form a single 32-bit hash value */
+ rowHash = mix_hash_value(nkeys, hash_array, isnull);
+
+ PG_RETURN_BOOL(rowHash % modulus == remainder);
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fb961e4..ae7772d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -459,7 +459,7 @@ static void RangeVarCallbackForAlterRelation(const RangeVar *rv, Oid relid,
static bool is_partition_attr(Relation rel, AttrNumber attnum, bool *used_in_expr);
static PartitionSpec *transformPartitionSpec(Relation rel, PartitionSpec *partspec, char *strategy);
static void ComputePartitionAttrs(Relation rel, List *partParams, AttrNumber *partattrs,
- List **partexprs, Oid *partopclass, Oid *partcollation);
+ List **partexprs, Oid *partopclass, Oid *partcollation, char strategy);
static void CreateInheritance(Relation child_rel, Relation parent_rel);
static void RemoveInheritance(Relation child_rel, Relation parent_rel);
static ObjectAddress ATExecAttachPartition(List **wqueue, Relation rel,
@@ -822,7 +822,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
&strategy);
ComputePartitionAttrs(rel, stmt->partspec->partParams,
partattrs, &partexprs, partopclass,
- partcollation);
+ partcollation, strategy);
partnatts = list_length(stmt->partspec->partParams);
StorePartitionKey(rel, strategy, partnatts, partattrs, partexprs,
@@ -13129,6 +13129,8 @@ transformPartitionSpec(Relation rel, PartitionSpec *partspec, char *strategy)
*strategy = PARTITION_STRATEGY_LIST;
else if (!pg_strcasecmp(partspec->strategy, "range"))
*strategy = PARTITION_STRATEGY_RANGE;
+ else if (!pg_strcasecmp(partspec->strategy, "hash"))
+ *strategy = PARTITION_STRATEGY_HASH;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -13161,6 +13163,7 @@ transformPartitionSpec(Relation rel, PartitionSpec *partspec, char *strategy)
errmsg("column \"%s\" appears more than once in partition key",
pelem->name),
parser_errposition(pstate, pelem->location)));
+
}
if (pelem->expr)
@@ -13184,10 +13187,13 @@ transformPartitionSpec(Relation rel, PartitionSpec *partspec, char *strategy)
*/
static void
ComputePartitionAttrs(Relation rel, List *partParams, AttrNumber *partattrs,
- List **partexprs, Oid *partopclass, Oid *partcollation)
+ List **partexprs, Oid *partopclass, Oid *partcollation,
+ char strategy)
{
int attn;
ListCell *lc;
+ char *am_method;
+ Oid am_oid;
attn = 0;
foreach(lc, partParams)
@@ -13330,25 +13336,37 @@ ComputePartitionAttrs(Relation rel, List *partParams, AttrNumber *partattrs,
partcollation[attn] = attcollation;
/*
- * Identify a btree opclass to use. Currently, we use only btree
- * operators, which seems enough for list and range partitioning.
+ * Identify the appropriate operator class. For list and range
+ * partitioning, we use a btree operator class; hash partitioning uses
+ * a hash operator class
*/
+ if (strategy == PARTITION_STRATEGY_HASH)
+ {
+ am_method = "hash";
+ am_oid = HASH_AM_OID;
+ }
+ else
+ {
+ am_method = "btree";
+ am_oid = BTREE_AM_OID;
+ }
+
if (!pelem->opclass)
{
- partopclass[attn] = GetDefaultOpClass(atttype, BTREE_AM_OID);
+ partopclass[attn] = GetDefaultOpClass(atttype, am_oid);
if (!OidIsValid(partopclass[attn]))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("data type %s has no default btree operator class",
- format_type_be(atttype)),
- errhint("You must specify a btree operator class or define a default btree operator class for the data type.")));
+ errmsg("data type %s has no default operator class for access method \"%s\"",
+ format_type_be(atttype), am_method),
+ errhint("You must specify an operator class or define a default operator class for the data type")));
}
else
partopclass[attn] = ResolveOpClass(pelem->opclass,
atttype,
- "btree",
- BTREE_AM_OID);
+ am_method,
+ am_oid);
attn++;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7811ad5..70e8dd4 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4444,6 +4444,8 @@ _copyPartitionBoundSpec(const PartitionBoundSpec *from)
PartitionBoundSpec *newnode = makeNode(PartitionBoundSpec);
COPY_SCALAR_FIELD(strategy);
+ COPY_SCALAR_FIELD(modulus);
+ COPY_SCALAR_FIELD(remainder);
COPY_NODE_FIELD(listdatums);
COPY_NODE_FIELD(lowerdatums);
COPY_NODE_FIELD(upperdatums);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index c9a8c34..200ed7a 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2838,6 +2838,8 @@ static bool
_equalPartitionBoundSpec(const PartitionBoundSpec *a, const PartitionBoundSpec *b)
{
COMPARE_SCALAR_FIELD(strategy);
+ COMPARE_SCALAR_FIELD(modulus);
+ COMPARE_SCALAR_FIELD(remainder);
COMPARE_NODE_FIELD(listdatums);
COMPARE_NODE_FIELD(lowerdatums);
COMPARE_NODE_FIELD(upperdatums);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 4949d58..369b3b0 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3543,6 +3543,8 @@ _outPartitionBoundSpec(StringInfo str, const PartitionBoundSpec *node)
WRITE_NODE_TYPE("PARTITIONBOUND");
WRITE_CHAR_FIELD(strategy);
+ WRITE_INT_FIELD(modulus);
+ WRITE_INT_FIELD(remainder);
WRITE_NODE_FIELD(listdatums);
WRITE_NODE_FIELD(lowerdatums);
WRITE_NODE_FIELD(upperdatums);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index e24f5d6..7ab8324 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -2373,6 +2373,8 @@ _readPartitionBoundSpec(void)
READ_LOCALS(PartitionBoundSpec);
READ_CHAR_FIELD(strategy);
+ READ_INT_FIELD(modulus);
+ READ_INT_FIELD(remainder);
READ_NODE_FIELD(listdatums);
READ_NODE_FIELD(lowerdatums);
READ_NODE_FIELD(upperdatums);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 2822331..d2dde4f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -579,7 +579,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> partbound_datum
%type <list> partbound_datum_list
%type <partrange_datum> PartitionRangeDatum
-%type <list> range_datum_list
+%type <list> range_datum_list hash_partbound
+%type <defelt> hash_partbound_elem
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -2652,8 +2653,60 @@ alter_identity_column_option:
;
ForValues:
+ /* a HASH partition*/
+ FOR VALUES WITH '(' hash_partbound ')'
+ {
+ ListCell *lc;
+ PartitionBoundSpec *n = makeNode(PartitionBoundSpec);
+
+ n->strategy = PARTITION_STRATEGY_HASH;
+ n->modulus = n->remainder = -1;
+
+ foreach (lc, $5)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "modulus") == 0)
+ {
+ if (n->modulus != -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("modulus for hash partition provided more than once"),
+ parser_errposition(opt->location)));
+ n->modulus = defGetInt32(opt);
+ }
+ else if (strcmp(opt->defname, "remainder") == 0)
+ {
+ if (n->remainder != -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("remainder for hash partition provided more than once"),
+ parser_errposition(opt->location)));
+ n->remainder = defGetInt32(opt);
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized hash partition bound specification \"%s\"",
+ opt->defname),
+ parser_errposition(opt->location)));
+ }
+
+ if (n->modulus == -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("modulus for hash partition must be specified")));
+ if (n->remainder == -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("remainder for hash partition must be specified")));
+
+ n->location = @3;
+
+ $$ = (Node *) n;
+ }
/* a LIST partition */
- FOR VALUES IN_P '(' partbound_datum_list ')'
+ | FOR VALUES IN_P '(' partbound_datum_list ')'
{
PartitionBoundSpec *n = makeNode(PartitionBoundSpec);
@@ -2678,6 +2731,24 @@ ForValues:
}
;
+hash_partbound_elem:
+ NonReservedWord Iconst
+ {
+ $$ = makeDefElem($1, (Node *)makeInteger($2), @1);
+ }
+ ;
+
+hash_partbound:
+ hash_partbound_elem
+ {
+ $$ = list_make1($1);
+ }
+ | hash_partbound ',' hash_partbound_elem
+ {
+ $$ = lappend($1, $3);
+ }
+ ;
+
partbound_datum:
Sconst { $$ = makeStringConst($1, @1); }
| NumericOnly { $$ = makeAConst($1, @1); }
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index beb0995..7f79a9f 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3294,7 +3294,30 @@ transformPartitionBound(ParseState *pstate, Relation parent, Node *bound)
result_spec = copyObject(spec);
- if (strategy == PARTITION_STRATEGY_LIST)
+ if (strategy == PARTITION_STRATEGY_HASH)
+ {
+ if (spec->strategy != PARTITION_STRATEGY_HASH)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("invalid bound specification for a hash partition"),
+ parser_errposition(pstate, exprLocation(bound))));
+
+ if (spec->modulus <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("modulus for hash partition must be a positive integer")));
+
+ if (spec->remainder < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("remainder for hash partition must be a non-negative integer")));
+
+ if (spec->remainder >= spec->modulus)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("modulus for hash partition must be greater than remainder")));
+ }
+ else if (strategy == PARTITION_STRATEGY_LIST)
{
ListCell *cell;
char *colname;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 9234bc2..6914528 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1652,6 +1652,10 @@ pg_get_partkeydef_worker(Oid relid, int prettyFlags,
switch (form->partstrat)
{
+ case PARTITION_STRATEGY_HASH:
+ if (!attrsOnly)
+ appendStringInfo(&buf, "HASH");
+ break;
case PARTITION_STRATEGY_LIST:
if (!attrsOnly)
appendStringInfo(&buf, "LIST");
@@ -8649,6 +8653,15 @@ get_rule_expr(Node *node, deparse_context *context,
switch (spec->strategy)
{
+ case PARTITION_STRATEGY_HASH:
+ Assert(spec->modulus > 0 && spec->remainder >= 0);
+ Assert(spec->modulus > spec->remainder);
+
+ appendStringInfoString(buf, "FOR VALUES");
+ appendStringInfo(buf, " WITH (modulus %d, remainder %d)",
+ spec->modulus, spec->remainder);
+ break;
+
case PARTITION_STRATEGY_LIST:
Assert(spec->listdatums != NIL);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 2abd087..d563746 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2046,7 +2046,7 @@ psql_completion(const char *text, int start, int end)
else if (TailMatches3("ATTACH", "PARTITION", MatchAny))
COMPLETE_WITH_CONST("FOR VALUES");
else if (TailMatches2("FOR", "VALUES"))
- COMPLETE_WITH_LIST2("FROM (", "IN (");
+ COMPLETE_WITH_LIST3("FROM (", "IN (", "WITH (");
/*
* If we have ALTER TABLE <foo> DETACH PARTITION, provide a list of
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 460cdb9..de09809 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5472,6 +5472,10 @@ DESCR("list files in the log directory");
DATA(insert OID = 3354 ( pg_ls_waldir PGNSP PGUID 12 10 20 0 0 f f f f t t v s 0 0 2249 "" "{25,20,1184}" "{o,o,o}" "{name,size,modification}" _null_ _null_ pg_ls_waldir _null_ _null_ _null_ ));
DESCR("list of files in the WAL directory");
+/* hash partitioning constraint function */
+DATA(insert OID = 5028 ( satisfies_hash_partition PGNSP PGUID 12 1 0 2276 0 f f f f f f i s 3 0 16 "23 23 1007" _null_ _null_ _null_ _null_ _null_ satisfies_hash_partition _null_ _null_ _null_ ));
+DESCR("hash partition CHECK constraint");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4b8727e..6573114 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -773,11 +773,13 @@ typedef struct PartitionElem
typedef struct PartitionSpec
{
NodeTag type;
- char *strategy; /* partitioning strategy ('list' or 'range') */
+ char *strategy; /* partitioning strategy ('hash', 'list' or
+ * 'range') */
List *partParams; /* List of PartitionElems */
int location; /* token location, or -1 if unknown */
} PartitionSpec;
+#define PARTITION_STRATEGY_HASH 'h'
#define PARTITION_STRATEGY_LIST 'l'
#define PARTITION_STRATEGY_RANGE 'r'
@@ -790,6 +792,10 @@ typedef struct PartitionBoundSpec
char strategy;
+ /* Hash partition specs */
+ int modulus;
+ int remainder;
+
/* List partition values */
List *listdatums;
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index c88fd76..efd8040 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3224,6 +3224,7 @@ SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::reg
CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS);
ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
ERROR: partition "fail_part" would overlap partition "part_1"
+DROP TABLE fail_part;
-- check validation when attaching list partitions
CREATE TABLE list_parted2 (
a int,
@@ -3302,6 +3303,59 @@ DETAIL: "part_5" is already a child of "list_parted2".
ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0);
ERROR: circular inheritance not allowed
DETAIL: "list_parted2" is already a child of "list_parted2".
+-- check validation when attaching hash partitions
+-- The default hash functions as they exist today aren't portable, they can
+-- return different results on different machines. Depending upon how the
+-- values are hashed, the row may map to different partitions, which result in
+-- regression failure. To avoid this, let's create a non-default hash function
+-- that just returns the input value unchanged.
+CREATE OR REPLACE FUNCTION dummy_hashint4(a int4) RETURNS int4 AS
+$$ BEGIN RETURN a; END; $$ LANGUAGE 'plpgsql' IMMUTABLE;
+CREATE OPERATOR CLASS custom_opclass FOR TYPE int4 USING HASH AS
+OPERATOR 1 = , FUNCTION 1 dummy_hashint4(int4);
+-- check that the new partition won't overlap with an existing partition
+CREATE TABLE hash_parted (
+ a int,
+ b int
+) PARTITION BY HASH (a custom_opclass);
+CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (modulus 4, remainder 0);
+CREATE TABLE fail_part (LIKE hpart_1);
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 8, remainder 4);
+ERROR: partition "fail_part" would overlap partition "hpart_1"
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 8, remainder 0);
+ERROR: partition "fail_part" would overlap partition "hpart_1"
+DROP TABLE fail_part;
+-- check validation when attaching hash partitions
+-- check that violating rows are correctly reported
+CREATE TABLE hpart_2 (LIKE hash_parted);
+INSERT INTO hpart_2 VALUES (3, 0);
+ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (modulus 4, remainder 1);
+ERROR: partition constraint is violated by some row
+-- should be ok after deleting the bad row
+DELETE FROM hpart_2;
+ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (modulus 4, remainder 1);
+-- check that leaf partitions are scanned when attaching a partitioned
+-- table
+CREATE TABLE hpart_5 (
+ LIKE hash_parted
+) PARTITION BY LIST (b);
+-- check that violating rows are correctly reported
+CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3');
+INSERT INTO hpart_5_a (a, b) VALUES (7, 1);
+ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (modulus 4, remainder 2);
+ERROR: partition constraint is violated by some row
+-- should be ok after deleting the bad row
+DELETE FROM hpart_5_a;
+ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (modulus 4, remainder 2);
+-- check that the table being attach is with valid modulus and remainder value
+CREATE TABLE fail_part(LIKE hash_parted);
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 0, remainder 1);
+ERROR: modulus for hash partition must be a positive integer
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 8, remainder 8);
+ERROR: modulus for hash partition must be greater than remainder
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 3, remainder 2);
+ERROR: every hash partition modulus must be a factor of the next larger modulus
+DROP TABLE fail_part;
--
-- DETACH PARTITION
--
@@ -3313,12 +3367,17 @@ DROP TABLE regular_table;
-- check that the partition being detached exists at all
ALTER TABLE list_parted2 DETACH PARTITION part_4;
ERROR: relation "part_4" does not exist
+ALTER TABLE hash_parted DETACH PARTITION hpart_4;
+ERROR: relation "hpart_4" does not exist
-- check that the partition being detached is actually a partition of the parent
CREATE TABLE not_a_part (a int);
ALTER TABLE list_parted2 DETACH PARTITION not_a_part;
ERROR: relation "not_a_part" is not a partition of relation "list_parted2"
ALTER TABLE list_parted2 DETACH PARTITION part_1;
ERROR: relation "part_1" is not a partition of relation "list_parted2"
+ALTER TABLE hash_parted DETACH PARTITION not_a_part;
+ERROR: relation "not_a_part" is not a partition of relation "hash_parted"
+DROP TABLE not_a_part;
-- check that, after being detached, attinhcount/coninhcount is dropped to 0 and
-- attislocal/conislocal is set to true
ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
@@ -3338,9 +3397,9 @@ SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::r
DROP TABLE part_3_4;
-- Check ALTER TABLE commands for partitioned tables and partitions
-- cannot add/drop column to/from *only* the parent
-ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
+ALTER TABLE ONLY list_parted ADD COLUMN c int;
ERROR: column must be added to child tables too
-ALTER TABLE ONLY list_parted2 DROP COLUMN b;
+ALTER TABLE ONLY list_parted DROP COLUMN b;
ERROR: cannot drop column from only the partitioned table when partitions exist
HINT: Do not specify the ONLY keyword.
-- cannot add a column to partition or drop an inherited one
@@ -3401,6 +3460,9 @@ ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
ERROR: cannot alter type of column named in partition key
-- cleanup
DROP TABLE list_parted, list_parted2, range_parted;
+DROP TABLE hash_parted;
+DROP OPERATOR CLASS custom_opclass USING HASH;
+DROP FUNCTION dummy_hashint4(int4);
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
create table p1 (b int, a int not null) partition by range (b);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 39edf04..b75d650 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -340,11 +340,6 @@ CREATE TABLE partitioned (
) PARTITION BY RANGE (const_func());
ERROR: cannot use constant expression as partition key
DROP FUNCTION const_func();
--- only accept "list" and "range" as partitioning strategy
-CREATE TABLE partitioned (
- a int
-) PARTITION BY HASH (a);
-ERROR: unrecognized partitioning strategy "hash"
-- specified column must be present in the table
CREATE TABLE partitioned (
a int
@@ -371,8 +366,8 @@ ERROR: partition key expressions cannot contain whole-row references
CREATE TABLE partitioned (
a point
) PARTITION BY LIST (a);
-ERROR: data type point has no default btree operator class
-HINT: You must specify a btree operator class or define a default btree operator class for the data type.
+ERROR: data type point has no default operator class for access method "btree"
+HINT: You must specify an operator class or define a default operator class for the data type
CREATE TABLE partitioned (
a point
) PARTITION BY LIST (a point_ops);
@@ -380,8 +375,8 @@ ERROR: operator class "point_ops" does not exist for access method "btree"
CREATE TABLE partitioned (
a point
) PARTITION BY RANGE (a);
-ERROR: data type point has no default btree operator class
-HINT: You must specify a btree operator class or define a default btree operator class for the data type.
+ERROR: data type point has no default operator class for access method "btree"
+HINT: You must specify an operator class or define a default operator class for the data type
CREATE TABLE partitioned (
a point
) PARTITION BY RANGE (a point_ops);
@@ -467,6 +462,11 @@ CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
ERROR: invalid bound specification for a list partition
LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) T...
^
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (modulus 10, remainder 1);
+ERROR: invalid bound specification for a list partition
+LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES WITH (modu...
+ ^
-- specified literal can't be cast to the partition column data type
CREATE TABLE bools (
a bool
@@ -500,6 +500,30 @@ ERROR: cannot specify finite value after UNBOUNDED
LINE 1: ...ge_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNB...
^
DROP TABLE range_parted_multicol;
+-- trying to specify modulus and remainder for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (modulus 10, remainder 1);
+ERROR: invalid bound specification for a range partition
+LINE 1: ...LE fail_part PARTITION OF range_parted FOR VALUES WITH (modu...
+ ^
+-- check partition bound syntax for the hash partition
+CREATE TABLE hash_parted (
+ a int
+) PARTITION BY HASH (a);
+CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (modulus 10, remainder 1);
+CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (modulus 50, remainder 0);
+-- modulus 25 is factor of modulus of 50 but 10 is not factor of 25.
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (modulus 25, remainder 2);
+ERROR: every hash partition modulus must be a factor of the next larger modulus
+-- trying to specify range for hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+ERROR: invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a',...
+ ^
+-- trying to specify list value for hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+ERROR: invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+ ^
-- check if compatible with the specified parent
-- cannot create as partition of a non-partitioned table
CREATE TABLE unparted (
@@ -507,6 +531,8 @@ CREATE TABLE unparted (
);
CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
ERROR: "unparted" is not partitioned
+CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (modulus 2, remainder 1);
+ERROR: "unparted" is not partitioned
DROP TABLE unparted;
-- cannot create a permanent rel as partition of a temp rel
CREATE TEMP TABLE temp_parted (
@@ -514,6 +540,8 @@ CREATE TEMP TABLE temp_parted (
) PARTITION BY LIST (a);
CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
+CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES WITH (modulus 2, remainder 1);
+ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
DROP TABLE temp_parted;
-- cannot create a table with oids as partition of table without oids
CREATE TABLE no_oids_parted (
@@ -521,6 +549,8 @@ CREATE TABLE no_oids_parted (
) PARTITION BY RANGE (a) WITHOUT OIDS;
CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10) WITH OIDS;
ERROR: cannot create table with OIDs as partition of table without OIDs
+CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES WITH (modulus 2, remainder 1) WITH OIDS;
+ERROR: cannot create table with OIDs as partition of table without OIDs
DROP TABLE no_oids_parted;
-- If the partitioned table has oids, then the partition must have them.
-- If the WITHOUT OIDS option is specified for partition, it is overridden.
@@ -528,6 +558,10 @@ CREATE TABLE oids_parted (
a int
) PARTITION BY RANGE (a) WITH OIDS;
CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (10) WITHOUT OIDS;
+CREATE TABLE fail_part PARTITION OF oids_parted FOR VALUES WITH (modulus 2, remainder 1) WITHOUT OIDS;
+ERROR: invalid bound specification for a range partition
+LINE 1: ...BLE fail_part PARTITION OF oids_parted FOR VALUES WITH (modu...
+ ^
\d+ part_forced_oids
Table "public.part_forced_oids"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
@@ -587,6 +621,23 @@ ERROR: partition "fail_part" would overlap partition "part12"
-- more specific ranges
CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded);
ERROR: partition "fail_part" would overlap partition "part10"
+-- check for partition bound overlap and other invalid specifications for the hash partition
+CREATE TABLE hash_parted2 (
+ a varchar
+) PARTITION BY HASH (a);
+CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 4, remainder 2);
+CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 0);
+CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 4);
+CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 5);
+-- overlap with part_4
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (modulus 2, remainder 1);
+ERROR: partition "fail_part" would overlap partition "h2part_4"
+-- modulus must be greater than zero
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (modulus 0, remainder 1);
+ERROR: modulus for hash partition must be a positive integer
+-- remainder must be greater than or equal to zero and less than modulus
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 8);
+ERROR: modulus for hash partition must be greater than remainder
-- check schema propagation from parent
CREATE TABLE parted (
a text,
@@ -735,6 +786,8 @@ Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS N
DROP TABLE range_parted4;
-- cleanup
DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
+DROP TABLE hash_parted;
+DROP TABLE hash_parted2;
-- comments on partitioned tables columns
CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
COMMENT ON TABLE parted_col_comment IS 'Am partitioned table';
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 8b0752a..97cbbca 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -314,8 +314,54 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
part_null | | 1 | 1
(9 rows)
+-- direct partition inserts should check hash partition bound constraint
+-- create custom operator class and hash function, for the same reason
+-- explained in alter_table.sql
+create or replace function dummy_hashint4(a int4) returns int4 as
+$$ begin return a; end; $$ language 'plpgsql' immutable;
+create operator class custom_opclass for type int4 using hash as
+operator 1 = , function 1 dummy_hashint4(int4);
+create table hash_parted (
+ a int
+) partition by hash (a custom_opclass);
+create table hpart1 partition of hash_parted for values with (modulus 4, remainder 0);
+create table hpart2 partition of hash_parted for values with (modulus 4, remainder 1);
+create table hpart3 partition of hash_parted for values with (modulus 4, remainder 2);
+create table hpart4 partition of hash_parted for values with (modulus 4, remainder 3);
+insert into hash_parted values(generate_series(1,10));
+-- direct insert of values divisible by 4 - ok;
+insert into hpart1 values(12),(16);
+-- fail;
+insert into hpart1 values(11);
+ERROR: new row for relation "hpart1" violates partition constraint
+DETAIL: Failing row contains (11).
+-- 11 % 4 -> 3 remainder i.e. valid data for hpart4 partition
+insert into hpart4 values(11);
+-- view data
+select tableoid::regclass as part, a, a%4 as "remainder = a % 4"
+from hash_parted order by part;
+ part | a | remainder = a % 4
+--------+----+-------------------
+ hpart1 | 4 | 0
+ hpart1 | 8 | 0
+ hpart1 | 12 | 0
+ hpart1 | 16 | 0
+ hpart2 | 1 | 1
+ hpart2 | 5 | 1
+ hpart2 | 9 | 1
+ hpart3 | 2 | 2
+ hpart3 | 6 | 2
+ hpart3 | 10 | 2
+ hpart4 | 3 | 3
+ hpart4 | 7 | 3
+ hpart4 | 11 | 3
+(13 rows)
+
-- cleanup
drop table range_parted, list_parted;
+drop table hash_parted;
+drop operator class custom_opclass using hash;
+drop function dummy_hashint4(int4);
-- more tests for certain multi-level partitioning scenarios
create table mlparted (a int, b int) partition by range (a, b);
create table mlparted1 (b int not null, a int not null) partition by range ((b+0));
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 9366f04..55fbc9e 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -218,5 +218,34 @@ ERROR: new row for relation "part_b_10_b_20" violates partition constraint
DETAIL: Failing row contains (b, 9).
-- ok
update range_parted set b = b + 1 where b = 10;
+-- create custom operator class and hash function, for the same reason
+-- explained in alter_table.sql
+create or replace function dummy_hashint4(a int4) returns int4 as
+$$ begin return a; end; $$ language 'plpgsql' immutable;
+create operator class custom_opclass for type int4 using hash as
+operator 1 = , function 1 dummy_hashint4(int4);
+create table hash_parted (
+ a int,
+ b int
+) partition by hash (a custom_opclass, b custom_opclass);
+create table hpart1 partition of hash_parted for values with (modulus 2, remainder 1);
+create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2);
+create table hpart3 partition of hash_parted for values with (modulus 8, remainder 0);
+create table hpart4 partition of hash_parted for values with (modulus 8, remainder 4);
+insert into hpart1 values (1, 1);
+insert into hpart2 values (2, 2);
+insert into hpart4 values (12, 12);
+-- fail
+update hpart1 set a = 4, b=4 where a = 1;
+ERROR: new row for relation "hpart1" violates partition constraint
+DETAIL: Failing row contains (4, 4).
+update hash_parted set b = b - 1 where b = 1;
+ERROR: new row for relation "hpart1" violates partition constraint
+DETAIL: Failing row contains (1, 0).
+-- ok
+update hash_parted set b = b + 8 where b = 1;
-- cleanup
drop table range_parted;
+drop table hash_parted;
+drop operator class custom_opclass using hash;
+drop function dummy_hashint4(int4);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index c0e2972..3d43494 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2075,6 +2075,7 @@ SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::reg
-- check that the new partition won't overlap with an existing partition
CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS);
ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1);
+DROP TABLE fail_part;
-- check validation when attaching list partitions
CREATE TABLE list_parted2 (
@@ -2160,6 +2161,62 @@ ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2);
ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b');
ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0);
+-- check validation when attaching hash partitions
+
+-- The default hash functions as they exist today aren't portable, they can
+-- return different results on different machines. Depending upon how the
+-- values are hashed, the row may map to different partitions, which result in
+-- regression failure. To avoid this, let's create a non-default hash function
+-- that just returns the input value unchanged.
+CREATE OR REPLACE FUNCTION dummy_hashint4(a int4) RETURNS int4 AS
+$$ BEGIN RETURN a; END; $$ LANGUAGE 'plpgsql' IMMUTABLE;
+CREATE OPERATOR CLASS custom_opclass FOR TYPE int4 USING HASH AS
+OPERATOR 1 = , FUNCTION 1 dummy_hashint4(int4);
+
+-- check that the new partition won't overlap with an existing partition
+CREATE TABLE hash_parted (
+ a int,
+ b int
+) PARTITION BY HASH (a custom_opclass);
+CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (modulus 4, remainder 0);
+CREATE TABLE fail_part (LIKE hpart_1);
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 8, remainder 4);
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 8, remainder 0);
+DROP TABLE fail_part;
+
+-- check validation when attaching hash partitions
+
+-- check that violating rows are correctly reported
+CREATE TABLE hpart_2 (LIKE hash_parted);
+INSERT INTO hpart_2 VALUES (3, 0);
+ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (modulus 4, remainder 1);
+
+-- should be ok after deleting the bad row
+DELETE FROM hpart_2;
+ALTER TABLE hash_parted ATTACH PARTITION hpart_2 FOR VALUES WITH (modulus 4, remainder 1);
+
+-- check that leaf partitions are scanned when attaching a partitioned
+-- table
+CREATE TABLE hpart_5 (
+ LIKE hash_parted
+) PARTITION BY LIST (b);
+
+-- check that violating rows are correctly reported
+CREATE TABLE hpart_5_a PARTITION OF hpart_5 FOR VALUES IN ('1', '2', '3');
+INSERT INTO hpart_5_a (a, b) VALUES (7, 1);
+ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (modulus 4, remainder 2);
+
+-- should be ok after deleting the bad row
+DELETE FROM hpart_5_a;
+ALTER TABLE hash_parted ATTACH PARTITION hpart_5 FOR VALUES WITH (modulus 4, remainder 2);
+
+-- check that the table being attach is with valid modulus and remainder value
+CREATE TABLE fail_part(LIKE hash_parted);
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 0, remainder 1);
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 8, remainder 8);
+ALTER TABLE hash_parted ATTACH PARTITION fail_part FOR VALUES WITH (modulus 3, remainder 2);
+DROP TABLE fail_part;
+
--
-- DETACH PARTITION
--
@@ -2171,12 +2228,16 @@ DROP TABLE regular_table;
-- check that the partition being detached exists at all
ALTER TABLE list_parted2 DETACH PARTITION part_4;
+ALTER TABLE hash_parted DETACH PARTITION hpart_4;
-- check that the partition being detached is actually a partition of the parent
CREATE TABLE not_a_part (a int);
ALTER TABLE list_parted2 DETACH PARTITION not_a_part;
ALTER TABLE list_parted2 DETACH PARTITION part_1;
+ALTER TABLE hash_parted DETACH PARTITION not_a_part;
+DROP TABLE not_a_part;
+
-- check that, after being detached, attinhcount/coninhcount is dropped to 0 and
-- attislocal/conislocal is set to true
ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
@@ -2187,8 +2248,8 @@ DROP TABLE part_3_4;
-- Check ALTER TABLE commands for partitioned tables and partitions
-- cannot add/drop column to/from *only* the parent
-ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
-ALTER TABLE ONLY list_parted2 DROP COLUMN b;
+ALTER TABLE ONLY list_parted ADD COLUMN c int;
+ALTER TABLE ONLY list_parted DROP COLUMN b;
-- cannot add a column to partition or drop an inherited one
ALTER TABLE part_2 ADD COLUMN c text;
@@ -2238,6 +2299,9 @@ ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
-- cleanup
DROP TABLE list_parted, list_parted2, range_parted;
+DROP TABLE hash_parted;
+DROP OPERATOR CLASS custom_opclass USING HASH;
+DROP FUNCTION dummy_hashint4(int4);
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 5a27743..eb909b4 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -350,11 +350,6 @@ CREATE TABLE partitioned (
) PARTITION BY RANGE (const_func());
DROP FUNCTION const_func();
--- only accept "list" and "range" as partitioning strategy
-CREATE TABLE partitioned (
- a int
-) PARTITION BY HASH (a);
-
-- specified column must be present in the table
CREATE TABLE partitioned (
a int
@@ -446,6 +441,8 @@ CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int);
CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
-- trying to specify range for list partitioned table
CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (modulus 10, remainder 1);
-- specified literal can't be cast to the partition column data type
CREATE TABLE bools (
@@ -473,6 +470,22 @@ CREATE TABLE range_parted_multicol (a int, b int, c int) PARTITION BY RANGE (a,
CREATE TABLE fail_part PARTITION OF range_parted_multicol FOR VALUES FROM (1, UNBOUNDED, 1) TO (UNBOUNDED, 1, 1);
DROP TABLE range_parted_multicol;
+-- trying to specify modulus and remainder for range partitioned table
+CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (modulus 10, remainder 1);
+
+-- check partition bound syntax for the hash partition
+CREATE TABLE hash_parted (
+ a int
+) PARTITION BY HASH (a);
+CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (modulus 10, remainder 1);
+CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (modulus 50, remainder 0);
+-- modulus 25 is factor of modulus of 50 but 10 is not factor of 25.
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (modulus 25, remainder 2);
+-- trying to specify range for hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+-- trying to specify list value for hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+
-- check if compatible with the specified parent
-- cannot create as partition of a non-partitioned table
@@ -480,6 +493,7 @@ CREATE TABLE unparted (
a int
);
CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
+CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (modulus 2, remainder 1);
DROP TABLE unparted;
-- cannot create a permanent rel as partition of a temp rel
@@ -487,6 +501,7 @@ CREATE TEMP TABLE temp_parted (
a int
) PARTITION BY LIST (a);
CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
+CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES WITH (modulus 2, remainder 1);
DROP TABLE temp_parted;
-- cannot create a table with oids as partition of table without oids
@@ -494,6 +509,7 @@ CREATE TABLE no_oids_parted (
a int
) PARTITION BY RANGE (a) WITHOUT OIDS;
CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10) WITH OIDS;
+CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES WITH (modulus 2, remainder 1) WITH OIDS;
DROP TABLE no_oids_parted;
-- If the partitioned table has oids, then the partition must have them.
@@ -502,6 +518,7 @@ CREATE TABLE oids_parted (
a int
) PARTITION BY RANGE (a) WITH OIDS;
CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (10) WITHOUT OIDS;
+CREATE TABLE fail_part PARTITION OF oids_parted FOR VALUES WITH (modulus 2, remainder 1) WITHOUT OIDS;
\d+ part_forced_oids
DROP TABLE oids_parted, part_forced_oids;
@@ -553,6 +570,21 @@ CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1,
-- more specific ranges
CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded);
+-- check for partition bound overlap and other invalid specifications for the hash partition
+CREATE TABLE hash_parted2 (
+ a varchar
+) PARTITION BY HASH (a);
+CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 4, remainder 2);
+CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 0);
+CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 4);
+CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 5);
+-- overlap with part_4
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (modulus 2, remainder 1);
+-- modulus must be greater than zero
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (modulus 0, remainder 1);
+-- remainder must be greater than or equal to zero and less than modulus
+CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (modulus 8, remainder 8);
+
-- check schema propagation from parent
CREATE TABLE parted (
@@ -622,6 +654,8 @@ DROP TABLE range_parted4;
-- cleanup
DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
+DROP TABLE hash_parted;
+DROP TABLE hash_parted2;
-- comments on partitioned tables columns
CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index db8967b..7a147e4 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -185,8 +185,41 @@ insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a);
insert into list_parted (b) values (1);
select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1;
+-- direct partition inserts should check hash partition bound constraint
+
+-- create custom operator class and hash function, for the same reason
+-- explained in alter_table.sql
+create or replace function dummy_hashint4(a int4) returns int4 as
+$$ begin return a; end; $$ language 'plpgsql' immutable;
+create operator class custom_opclass for type int4 using hash as
+operator 1 = , function 1 dummy_hashint4(int4);
+
+create table hash_parted (
+ a int
+) partition by hash (a custom_opclass);
+create table hpart1 partition of hash_parted for values with (modulus 4, remainder 0);
+create table hpart2 partition of hash_parted for values with (modulus 4, remainder 1);
+create table hpart3 partition of hash_parted for values with (modulus 4, remainder 2);
+create table hpart4 partition of hash_parted for values with (modulus 4, remainder 3);
+
+insert into hash_parted values(generate_series(1,10));
+
+-- direct insert of values divisible by 4 - ok;
+insert into hpart1 values(12),(16);
+-- fail;
+insert into hpart1 values(11);
+-- 11 % 4 -> 3 remainder i.e. valid data for hpart4 partition
+insert into hpart4 values(11);
+
+-- view data
+select tableoid::regclass as part, a, a%4 as "remainder = a % 4"
+from hash_parted order by part;
+
-- cleanup
drop table range_parted, list_parted;
+drop table hash_parted;
+drop operator class custom_opclass using hash;
+drop function dummy_hashint4(int4);
-- more tests for certain multi-level partitioning scenarios
create table mlparted (a int, b int) partition by range (a, b);
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index 6637119..873801b 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -125,5 +125,33 @@ update range_parted set b = b - 1 where b = 10;
-- ok
update range_parted set b = b + 1 where b = 10;
+-- create custom operator class and hash function, for the same reason
+-- explained in alter_table.sql
+create or replace function dummy_hashint4(a int4) returns int4 as
+$$ begin return a; end; $$ language 'plpgsql' immutable;
+create operator class custom_opclass for type int4 using hash as
+operator 1 = , function 1 dummy_hashint4(int4);
+
+create table hash_parted (
+ a int,
+ b int
+) partition by hash (a custom_opclass, b custom_opclass);
+create table hpart1 partition of hash_parted for values with (modulus 2, remainder 1);
+create table hpart2 partition of hash_parted for values with (modulus 4, remainder 2);
+create table hpart3 partition of hash_parted for values with (modulus 8, remainder 0);
+create table hpart4 partition of hash_parted for values with (modulus 8, remainder 4);
+insert into hpart1 values (1, 1);
+insert into hpart2 values (2, 2);
+insert into hpart4 values (12, 12);
+
+-- fail
+update hpart1 set a = 4, b=4 where a = 1;
+update hash_parted set b = b - 1 where b = 1;
+-- ok
+update hash_parted set b = b + 8 where b = 1;
+
-- cleanup
drop table range_parted;
+drop table hash_parted;
+drop operator class custom_opclass using hash;
+drop function dummy_hashint4(int4);
--
2.6.2