public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: Dmitry Koval <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Date: Fri, 19 Apr 2024 16:29:44 +0300
Message-ID: <CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAMbWs49c_7dT_oiP729b-JPRiDtTuDca3exN_cOZZwjGG-0bMA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAPpHfdv1ZkY9cPOqsLvDY9AA-fCYKHM4P07+-Mu+xq5o=daS6w@mail.gmail.com>
<CA+TgmoYcjL+w2BQzku5iNXKR5fyxJMSP3avQta8xngioTX7D7A@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAPpHfdv-uR663BO6G2kRuLEQjob+UBHuD3iyXF+qGHXQFRN0-Q@mail.gmail.com>
<[email protected]>
<CAPpHfdvShLh5txtQdh6=ZMCnzGZHv8W+RHAe8pY94rfzghWTAg@mail.gmail.com>
<[email protected]>
<[email protected]>
On Fri, Apr 19, 2024 at 2:26 AM Dmitry Koval <[email protected]> wrote:
>
> Hi!
>
> 18.04.2024 19:00, Alexander Lakhin wrote:
> > leaves a strange constraint:
> > \d+ t*
> > Table "public.tp_0"
> > ...
> > Not-null constraints:
> > "merge-16385-26BCB0-tmp_i_not_null" NOT NULL "i"
>
> Thanks!
> Attached fix (with test) for this case.
> The patch should be applied after patches
> v6-0001- ... .patch ... v6-0004- ... .patch
I've incorporated this fix with 0001 patch.
Also added to the patchset
005 – tab completion by Dagfinn [1]
006 – draft fix for table AM issue spotted by Alexander Lakhin [2]
007 – doc review by Justin [3]
I'm continuing work on this.
Links
1. https://www.postgresql.org/message-id/87plumiox2.fsf%40wibble.ilmari.org
2. https://www.postgresql.org/message-id/84ada05b-be5c-473e-6d1c-ebe5dd21b190%40gmail.com
3. https://www.postgresql.org/message-id/ZiGH0xc1lxJ71ZfB%40pryzbyj2023
------
Regards,
Alexander Korotkov
Attachments:
[application/octet-stream] v7-0001-Change-the-way-ATExecMergePartitions-handles-the-.patch (7.4K, ../CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com/2-v7-0001-Change-the-way-ATExecMergePartitions-handles-the-.patch)
download | inline diff:
From be331b8793b92fed797b76b48acb231c405c4a1a Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Fri, 19 Apr 2024 13:39:45 +0300
Subject: [PATCH v7 1/7] Change the way ATExecMergePartitions() handles the
name collision
The name collision happens when the name of the new partition is the same as
the name of one of the merging partitions. Currently, ATExecMergePartitions()
first gives the new partition a temporary name and then renames it when old
partitions are deleted. That negatively influences the naming of related
objects like indexes and constrains, which could inherit a temporary name.
This commit changes the implementation in the following way. A merging
partition gets renamed first, then the new partition is created with the
right name immediately. This resolves the issue of the naming of related
objects.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/edfbd846-dcc1-42d1-ac26-715691b687d3%40postgrespro.ru
Author: Dmitry Koval
Reviewed-by: Alexander Korotkov, Robert Haas
---
src/backend/commands/tablecmds.c | 61 +++++++++----------
src/test/regress/expected/partition_merge.out | 25 ++++++++
src/test/regress/sql/partition_merge.sql | 18 ++++++
3 files changed, 72 insertions(+), 32 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fbffaef1966..0f72ffa9abc 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21476,9 +21476,6 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
ListCell *listptr;
List *mergingPartitionsList = NIL;
Oid defaultPartOid;
- char tmpRelName[NAMEDATALEN];
- RangeVar *mergePartName = cmd->name;
- bool isSameName = false;
/*
* Lock all merged partitions, check them and create list with partitions
@@ -21500,8 +21497,28 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
* function transformPartitionCmdForMerge().
*/
if (equal(name, cmd->name))
+ {
/* One new partition can have the same name as merged partition. */
- isSameName = true;
+ char tmpRelName[NAMEDATALEN];
+
+ /* Generate temporary name. */
+ sprintf(tmpRelName, "merge-%u-%X-tmp", RelationGetRelid(rel), MyProcPid);
+
+ /*
+ * Rename the existing partition with a temporary name, leaving it
+ * free for the new partition. We don't need to care about this
+ * in the future because we're going to eventually drop the
+ * existing partition anyway.
+ */
+ RenameRelationInternal(RelationGetRelid(mergingPartition),
+ tmpRelName, false, false);
+
+ /*
+ * We must bump the command counter to make the new partition
+ * tuple visible for rename.
+ */
+ CommandCounterIncrement();
+ }
/* Store a next merging partition into the list. */
mergingPartitionsList = lappend(mergingPartitionsList,
@@ -21521,15 +21538,7 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
DetachPartitionFinalize(rel, mergingPartition, false, defaultPartOid);
}
- /* Create table for new partition, use partitioned table as model. */
- if (isSameName)
- {
- /* Create partition table with generated temporary name. */
- sprintf(tmpRelName, "merge-%u-%X-tmp", RelationGetRelid(rel), MyProcPid);
- mergePartName = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
- tmpRelName, -1);
- }
- createPartitionTable(mergePartName,
+ createPartitionTable(cmd->name,
makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
RelationGetRelationName(rel), -1),
context);
@@ -21540,17 +21549,11 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
* excessive, but this is the way we make sure nobody is planning queries
* involving merging partitions.
*/
- newPartRel = table_openrv(mergePartName, AccessExclusiveLock);
+ newPartRel = table_openrv(cmd->name, AccessExclusiveLock);
/* Copy data from merged partitions to new partition. */
moveMergedTablesRows(rel, mergingPartitionsList, newPartRel);
- /*
- * Attach a new partition to the partitioned table. wqueue = NULL:
- * verification for each cloned constraint is not need.
- */
- attachPartitionTable(NULL, rel, newPartRel, cmd->bound);
-
/* Unlock and drop merged partitions. */
foreach(listptr, mergingPartitionsList)
{
@@ -21569,18 +21572,12 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
list_free(mergingPartitionsList);
- /* Rename new partition if it is needed. */
- if (isSameName)
- {
- /*
- * We must bump the command counter to make the new partition tuple
- * visible for rename.
- */
- CommandCounterIncrement();
- /* Rename partition. */
- RenameRelationInternal(RelationGetRelid(newPartRel),
- cmd->name->relname, false, false);
- }
+ /*
+ * Attach a new partition to the partitioned table. wqueue = NULL:
+ * verification for each cloned constraint is not needed.
+ */
+ attachPartitionTable(NULL, rel, newPartRel, cmd->bound);
+
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 373d32948ca..2e0bfdc705d 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -746,4 +746,29 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
--
+-- Check the partition index name if the partition name is the same as one
+-- of the merged partitions.
+--
+CREATE TABLE t (i int, PRIMARY KEY(i)) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+CREATE INDEX tidx ON t(i);
+ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2;
+-- Indexname values should be 'tp_1_2_pkey' and 'tp_1_2_i_idx'.
+-- Not-null constraint name should be 'tp_1_2_i_not_null'.
+\d+ tp_1_2
+ Table "partitions_merge_schema.tp_1_2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i | integer | | not null | | plain | |
+Partition of: t FOR VALUES FROM (0) TO (2)
+Partition constraint: ((i IS NOT NULL) AND (i >= 0) AND (i < 2))
+Indexes:
+ "tp_1_2_pkey" PRIMARY KEY, btree (i)
+ "tp_1_2_i_idx" btree (i)
+Not-null constraints:
+ "tp_1_2_i_not_null" NOT NULL "i"
+
+DROP TABLE t;
+--
DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 6a0b35b1799..72b1cb0b35e 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -444,5 +444,23 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
+--
+-- Check the partition index name if the partition name is the same as one
+-- of the merged partitions.
+--
+CREATE TABLE t (i int, PRIMARY KEY(i)) PARTITION BY RANGE (i);
+
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+
+CREATE INDEX tidx ON t(i);
+ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2;
+
+-- Indexname values should be 'tp_1_2_pkey' and 'tp_1_2_i_idx'.
+-- Not-null constraint name should be 'tp_1_2_i_not_null'.
+\d+ tp_1_2
+
+DROP TABLE t;
+
--
DROP SCHEMA partitions_merge_schema;
--
2.39.3 (Apple Git-145)
[application/octet-stream] v7-0002-Verify-persistence-of-new-partitions-during-MERGE.patch (17.8K, ../CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com/3-v7-0002-Verify-persistence-of-new-partitions-during-MERGE.patch)
download | inline diff:
From ca4d010f40b1c10cfb72d72e41fda3c992f66235 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Thu, 18 Apr 2024 12:41:09 +0300
Subject: [PATCH v7 2/7] Verify persistence of new partitions during
MERGE/SPLIT operations
The createPartitionTable() function is responsible for creating new partitions
for ALTER TABLE ... MERGE PARTITIONS, and ALTER TABLE ... SPLIT PARTITION
commands. It emulates the behaviour of CREATE TABLE ... (LIKE ...), but
lacks of check for the result relation persistence. This commit adds
corresponding check similarly to what we have for CREATE TABLE ...
PARTITION OF command.
This commit also changes the signature of createPartitionTable() making it
take the parent's Relation itself instead of the name of the parent relation,
and return the Relation of new partition. That doesn't lead to
complications, because both callers have the parent table open and need to
open the new partition.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/dbc8b96c-3cf0-d1ee-860d-0e491da20485%40gmail.com
Author: Dmitry Koval
Reviewed-by: Alexander Korotkov, Robert Haas
---
src/backend/commands/tablecmds.c | 70 +++++++++------
src/test/regress/expected/partition_merge.out | 89 +++++++++++++++++++
src/test/regress/expected/partition_split.out | 29 ++++++
src/test/regress/sql/partition_merge.sql | 68 ++++++++++++++
src/test/regress/sql/partition_split.sql | 23 +++++
5 files changed, 254 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0f72ffa9abc..2025e68bdae 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21182,18 +21182,27 @@ moveSplitTableRows(Relation rel, Relation splitRel, List *partlist, List *newPar
/*
* createPartitionTable: create table for a new partition with given name
- * (newPartName) like table (modelRelName)
+ * (newPartName) like table (modelRel)
*
- * Emulates command: CREATE TABLE <newPartName> (LIKE <modelRelName>
+ * Emulates command: CREATE [TEMP] TABLE <newPartName> (LIKE <modelRel's name>
* INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)
+ * Function returns the created relation (locked in AccessExclusiveLock mode).
*/
-static void
-createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
+static Relation
+createPartitionTable(RangeVar *newPartName, Relation modelRel,
AlterTableUtilityContext *context)
{
CreateStmt *createStmt;
TableLikeClause *tlc;
PlannedStmt *wrapper;
+ Relation newRel;
+
+ /* If existing rel is temp, it must belong to this session */
+ if (modelRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
+ !modelRel->rd_islocaltemp)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create as partition of temporary relation of another session")));
createStmt = makeNode(CreateStmt);
createStmt->relation = newPartName;
@@ -21206,7 +21215,8 @@ createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
createStmt->if_not_exists = false;
tlc = makeNode(TableLikeClause);
- tlc->relation = modelRelName;
+ tlc->relation = makeRangeVar(get_namespace_name(RelationGetNamespace(modelRel)),
+ RelationGetRelationName(modelRel), -1);
/*
* Indexes will be inherited on "attach new partitions" stage, after data
@@ -21232,6 +21242,33 @@ createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
NULL,
None_Receiver,
NULL);
+
+ /*
+ * Open the new partition with no lock, because we already have
+ * AccessExclusiveLock placed there after creation.
+ */
+ newRel = table_openrv(newPartName, NoLock);
+
+ /*
+ * If the parent is permanent, so must be all of its partitions. Note
+ * that inheritance allows that case.
+ */
+ if (modelRel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
+ newRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create a temporary relation as partition of permanent relation \"%s\"",
+ RelationGetRelationName(modelRel))));
+
+ /* Permanent rels cannot inherit from temporary ones */
+ if (newRel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
+ modelRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create a permanent relation as partition of temporary relation \"%s\"",
+ RelationGetRelationName(modelRel))));
+
+ return newRel;
}
/*
@@ -21251,7 +21288,6 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
char tmpRelName[NAMEDATALEN];
List *newPartRels = NIL;
ObjectAddress object;
- RangeVar *parentName;
Oid defaultPartOid;
defaultPartOid = get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
@@ -21323,18 +21359,12 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
/* Create new partitions (like split partition), without indexes. */
- parentName = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
- RelationGetRelationName(rel), -1);
foreach(listptr, cmd->partlist)
{
SinglePartitionSpec *sps = (SinglePartitionSpec *) lfirst(listptr);
Relation newPartRel;
- createPartitionTable(sps->name, parentName, context);
-
- /* Open the new partition and acquire exclusive lock on it. */
- newPartRel = table_openrv(sps->name, AccessExclusiveLock);
-
+ newPartRel = createPartitionTable(sps->name, rel, context);
newPartRels = lappend(newPartRels, newPartRel);
}
@@ -21538,18 +21568,8 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
DetachPartitionFinalize(rel, mergingPartition, false, defaultPartOid);
}
- createPartitionTable(cmd->name,
- makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
- RelationGetRelationName(rel), -1),
- context);
-
- /*
- * Open the new partition and acquire exclusive lock on it. This will
- * stop all the operations with partitioned table. This might seem
- * excessive, but this is the way we make sure nobody is planning queries
- * involving merging partitions.
- */
- newPartRel = table_openrv(cmd->name, AccessExclusiveLock);
+ /* Create table for new partition, use partitioned table as model. */
+ newPartRel = createPartitionTable(cmd->name, rel, context);
/* Copy data from merged partitions to new partition. */
moveMergedTablesRows(rel, mergingPartitionsList, newPartRel);
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 2e0bfdc705d..cf961b041fb 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -746,6 +746,36 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
--
+-- Try to MERGE partitions of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TEMP TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+ oid | pg_get_expr | relpersistence
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | t
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | t
+(2 rows)
+
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ERROR: cannot create a permanent relation as partition of temporary relation "t"
+-- Partition should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+ oid | pg_get_expr | relpersistence
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | t
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | t
+(2 rows)
+
+DROP TABLE t;
+--
-- Check the partition index name if the partition name is the same as one
-- of the merged partitions.
--
@@ -771,4 +801,63 @@ Not-null constraints:
DROP TABLE t;
--
+-- Try mixing permanent and temporary partitions.
+--
+SET search_path = public, pg_temp;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+ oid | relpersistence
+-----+----------------
+ t | p
+(1 row)
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+ oid | pg_get_expr | relpersistence
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | p
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | p
+(2 rows)
+
+SET search_path = pg_temp, public;
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ERROR: cannot create a temporary relation as partition of permanent relation "t"
+RESET search_path;
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
+ERROR: cannot create a temporary relation as partition of permanent relation "t"
+DROP TABLE t;
+SET search_path = pg_temp, public;
+BEGIN;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+ oid | relpersistence
+-----+----------------
+ t | t
+(1 row)
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+ oid | pg_get_expr | relpersistence
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | t
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | t
+(2 rows)
+
+SET search_path = public, pg_temp;
+-- Can't merge temporary partitions into a persistent partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ERROR: cannot create a permanent relation as partition of temporary relation "t"
+ROLLBACK;
+RESET search_path;
+--
DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 419d169f036..660361cd20e 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1427,4 +1427,33 @@ ERROR: relation "t1pa" is not a partition of relation "t2"
DROP TABLE t2;
DROP TABLE t1;
--
+-- Try to SPLIT partition of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+ oid | pg_get_expr | relpersistence
+--------+----------------------------+----------------
+ tp_0_2 | FOR VALUES FROM (0) TO (2) | t
+(1 row)
+
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+ (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+ PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+ERROR: cannot create a permanent relation as partition of temporary relation "t"
+-- Partitions should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+ oid | pg_get_expr | relpersistence
+--------+----------------------------+----------------
+ tp_0_2 | FOR VALUES FROM (0) TO (2) | t
+(1 row)
+
+DROP TABLE t;
+--
DROP SCHEMA partition_split_schema;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 72b1cb0b35e..29ae6a09f13 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -444,6 +444,28 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
+--
+-- Try to MERGE partitions of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TEMP TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+
+-- Partition should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+DROP TABLE t;
+
--
-- Check the partition index name if the partition name is the same as one
-- of the merged partitions.
@@ -462,5 +484,51 @@ ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2;
DROP TABLE t;
+--
+-- Try mixing permanent and temporary partitions.
+--
+SET search_path = partitions_merge_schema, pg_temp, public;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+
+SET search_path = partitions_merge_schema, public;
+
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
+DROP TABLE t;
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+
+BEGIN;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+SET search_path = partitions_merge_schema, pg_temp, public;
+
+-- Can't merge temporary partitions into a persistent partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ROLLBACK;
+
+RESET search_path;
+
--
DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index b63532ee562..576f9f0f63c 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -844,5 +844,28 @@ ALTER TABLE t2 SPLIT PARTITION t1pa INTO
DROP TABLE t2;
DROP TABLE t1;
+--
+-- Try to SPLIT partition of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+ (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+ PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+
+-- Partitions should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+ FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+ WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+ ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+DROP TABLE t;
+
--
DROP SCHEMA partition_split_schema;
--
2.39.3 (Apple Git-145)
[application/octet-stream] v7-0005-Add-tab-completion-for-ALTER-TABLE-SPLIT-MERGE-PA.patch (2.5K, ../CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com/4-v7-0005-Add-tab-completion-for-ALTER-TABLE-SPLIT-MERGE-PA.patch)
download | inline diff:
From f20d063f436b126da5ef0b78f0fb90b698c965eb Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <[email protected]>
Date: Thu, 18 Apr 2024 17:47:22 +0100
Subject: [PATCH v7 5/7] =?UTF-8?q?Add=20tab=20completion=20for=20ALTER=20T?=
=?UTF-8?q?ABLE=20=E2=80=A6=20SPLIT|MERGE=20PARTITION(S)?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
---
src/bin/psql/tab-complete.c | 18 ++++++++++++++++--
1 file changed, 16 insertions(+), 2 deletions(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6fee3160f02..97cd5d9f628 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2353,6 +2353,7 @@ psql_completion(const char *text, int start, int end)
"OWNER TO", "SET", "VALIDATE CONSTRAINT",
"REPLICA IDENTITY", "ATTACH PARTITION",
"DETACH PARTITION", "FORCE ROW LEVEL SECURITY",
+ "SPLIT PARTITION", "MERGE PARTITIONS (",
"OF", "NOT OF");
/* ALTER TABLE xxx ADD */
else if (Matches("ALTER", "TABLE", MatchAny, "ADD"))
@@ -2609,10 +2610,10 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("FROM (", "IN (", "WITH (");
/*
- * If we have ALTER TABLE <foo> DETACH PARTITION, provide a list of
+ * If we have ALTER TABLE <foo> DETACH|SPLIT PARTITION, provide a list of
* partitions of <foo>.
*/
- else if (Matches("ALTER", "TABLE", MatchAny, "DETACH", "PARTITION"))
+ else if (Matches("ALTER", "TABLE", MatchAny, "DETACH|SPLIT", "PARTITION"))
{
set_completion_reference(prev3_wd);
COMPLETE_WITH_SCHEMA_QUERY(Query_for_partition_of_table);
@@ -2620,6 +2621,19 @@ psql_completion(const char *text, int start, int end)
else if (Matches("ALTER", "TABLE", MatchAny, "DETACH", "PARTITION", MatchAny))
COMPLETE_WITH("CONCURRENTLY", "FINALIZE");
+ /* ALTER TABLE <name> SPLIT PARTITION <name> */
+ else if (Matches("ALTER", "TABLE", MatchAny, "SPLIT", "PARTITION", MatchAny))
+ COMPLETE_WITH("INTO ( PARTITION");
+
+ /* ALTER TABLE <name> MERGE PARTITIONS ( */
+ else if (Matches("ALTER", "TABLE", MatchAny, "MERGE", "PARTITIONS", "("))
+ {
+ set_completion_reference(prev4_wd);
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_partition_of_table);
+ }
+ else if (Matches("ALTER", "TABLE", MatchAny, "MERGE", "PARTITIONS", "(*)"))
+ COMPLETE_WITH("INTO");
+
/* ALTER TABLE <name> OF */
else if (Matches("ALTER", "TABLE", MatchAny, "OF"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_composite_datatypes);
--
2.39.3 (Apple Git-145)
[application/octet-stream] v7-0003-Fix-error-message-in-check_partition_bounds_for_s.patch (3.9K, ../CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com/5-v7-0003-Fix-error-message-in-check_partition_bounds_for_s.patch)
download | inline diff:
From 204321a37aa69273a6438e26163eb769d49cac47 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Thu, 18 Apr 2024 12:56:51 +0300
Subject: [PATCH v7 3/7] Fix error message in
check_partition_bounds_for_split_range()
Currently, the error message is produced by a system of complex substitutions
making it quite untranslatable and hard to read. This commit splits this into
4 plain error messages suitable for translation.
Reported-by: Kyotaro Horiguchi
Discussion: https://postgr.es/m/20240408.152402.1485994009160660141.horikyota.ntt%40gmail.com
---
src/backend/partitioning/partbounds.c | 60 +++++++++++++++++----------
1 file changed, 39 insertions(+), 21 deletions(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index b08edf87a69..6c04d8bef5b 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5211,7 +5211,7 @@ check_partition_bounds_for_split_range(Relation parent,
if (first || last)
{
PartitionBoundSpec *split_spec = get_partition_bound_spec(splitPartOid, splitPartName);
- bool overlap = false;
+ PartitionRangeDatum *datum;
if (first)
{
@@ -5224,13 +5224,30 @@ check_partition_bounds_for_split_range(Relation parent,
key->partcollation,
lower->datums, lower->kind,
true, split_lower);
+ datum = cmpval ? list_nth(spec->lowerdatums, abs(cmpval) - 1) : NULL;
/*
* Lower bound of "spec" should be equal (or greater than or equal
* in case defaultPart=true) to lower bound of split partition.
*/
- if ((!defaultPart && cmpval) || (defaultPart && cmpval < 0))
- overlap = true;
+ if (!defaultPart)
+ {
+ if (cmpval != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("lower bound of partition \"%s\" is not equal to lower bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
+ else
+ {
+ if (cmpval < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("lower bound of partition \"%s\" is less than lower bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
}
else
{
@@ -5243,29 +5260,30 @@ check_partition_bounds_for_split_range(Relation parent,
key->partcollation,
upper->datums, upper->kind,
false, split_upper);
+ datum = cmpval ? list_nth(spec->lowerdatums, abs(cmpval) - 1) : NULL;
/*
* Upper bound of "spec" should be equal (or less than or equal in
* case defaultPart=true) to upper bound of split partition.
*/
- if ((!defaultPart && cmpval) || (defaultPart && cmpval > 0))
- overlap = true;
- }
-
- if (overlap)
- {
- PartitionRangeDatum *datum;
-
- datum = list_nth(first ? spec->lowerdatums : spec->upperdatums, abs(cmpval) - 1);
-
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("%s bound of partition \"%s\" is %s %s bound of split partition",
- first ? "lower" : "upper",
- relname,
- defaultPart ? (first ? "less than" : "greater than") : "not equal to",
- first ? "lower" : "upper"),
- parser_errposition(pstate, datum->location)));
+ if (!defaultPart)
+ {
+ if (cmpval != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("upper bound of partition \"%s\" is not equal to upper bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
+ else
+ {
+ if (cmpval > 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("upper bound of partition \"%s\" is greater than upper bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
}
}
}
--
2.39.3 (Apple Git-145)
[application/octet-stream] v7-0004-Rename-tables-in-tests-of-partition-MERGE-SPLIT-o.patch (202.8K, ../CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com/6-v7-0004-Rename-tables-in-tests-of-partition-MERGE-SPLIT-o.patch)
download | inline diff:
From e985c103b43cd6d31da294d8541407dc0c8dc131 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Fri, 19 Apr 2024 16:03:06 +0300
Subject: [PATCH v7 4/7] Rename tables in tests of partition MERGE/SPLIT
operations
Replace "salesman" with "salesperson", "salesmen" with "salespeople". The
names are both gramatically correct and gender-neutral.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/fdaa003e-919c-cbc9-4f0c-e4546e96bd65%40gmail.com
Reviewed-by: Robert Haas
---
src/test/regress/expected/partition_merge.out | 676 ++++-----
src/test/regress/expected/partition_split.out | 1238 ++++++++---------
src/test/regress/sql/partition_merge.sql | 160 +--
src/test/regress/sql/partition_split.sql | 280 ++--
4 files changed, 1177 insertions(+), 1177 deletions(-)
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index cf961b041fb..acacd81767c 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -10,12 +10,12 @@ SET search_path = partitions_merge_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_dec2021 PARTITION OF sales_range FOR VALUES FROM ('2021-12-01') TO ('2021-12-31');
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-15');
CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -54,7 +54,7 @@ DROP TABLE sales_range;
--
-- Add rows into partitioned table, then merge partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -115,68 +115,68 @@ SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemanam
(1 row)
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb_mar_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(10 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
-- Use indexscan for testing indexes
SET enable_seqscan = OFF;
SELECT * FROM sales_feb_mar_apr2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 11 | Trump | 380 | 04-06-2022
- 5 | Deev | 250 | 04-07-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 11 | Trump | 380 | 04-06-2022
+ 5 | Deev | 250 | 04-07-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(10 rows)
RESET enable_seqscan;
@@ -184,7 +184,7 @@ DROP TABLE sales_range;
--
-- Merge some partitions into DEFAULT partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -209,15 +209,15 @@ INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
-- name
ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022, sales_others) INTO sales_others;
select * from sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(7 rows)
-- show partitions with conditions:
@@ -239,7 +239,7 @@ DROP TABLE sales_range;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -248,219 +248,219 @@ CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1)
CREATE TABLE sales_jan2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1);
CREATE TABLE sales_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(9 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
(2 rows)
SELECT * FROM sales_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
(2 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
ALTER TABLE sales_date MERGE PARTITIONS (sales_jan2022, sales_feb2022) INTO sales_jan_feb2022;
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(11 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
(6 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
DROP TABLE sales_date;
--
-- Test: merge partitions of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_20 PARTITION OF salesmans FOR VALUES FROM (10) TO (20);
-CREATE TABLE salesmans20_30 PARTITION OF salesmans FOR VALUES FROM (20) TO (30);
-CREATE TABLE salesmans30_40 PARTITION OF salesmans FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_20 PARTITION OF salespeople FOR VALUES FROM (10) TO (20);
+CREATE TABLE salespeople20_30 PARTITION OF salespeople FOR VALUES FROM (20) TO (30);
+CREATE TABLE salespeople30_40 PARTITION OF salespeople FOR VALUES FROM (30) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
RAISE NOTICE 'trigger(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (10, 'May');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_20 VALUES (19, 'Ivanov');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+INSERT INTO salespeople10_20 VALUES (19, 'Ivanov');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (30, 'Ford');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+INSERT INTO salespeople10_40 VALUES (30, 'Ford');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
- 20 | Smirnoff
- 30 | Ford
+SELECT * FROM salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
+ 20 | Smirnoff
+ 30 | Ford
(4 rows)
-DROP TABLE salesmans;
+DROP TABLE salespeople;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: merge partitions with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create partitions with some deleted columns:
-CREATE TABLE salesmans10_20(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-CREATE TABLE salesmans20_30(salesman_id INT PRIMARY KEY, d2 INT, salesman_name VARCHAR(30));
-CREATE TABLE salesmans30_40(salesman_id INT PRIMARY KEY, d3 DATE, salesman_name VARCHAR(30));
-INSERT INTO salesmans10_20 VALUES ('dummy value 1', 19, 'Ivanov');
-INSERT INTO salesmans20_30 VALUES (20, 101, 'Smirnoff');
-INSERT INTO salesmans30_40 VALUES (31, now(), 'Popov');
-ALTER TABLE salesmans10_20 DROP COLUMN d1;
-ALTER TABLE salesmans20_30 DROP COLUMN d2;
-ALTER TABLE salesmans30_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20);
-ALTER TABLE salesmans ATTACH PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30);
-ALTER TABLE salesmans ATTACH PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
-select * from salesmans;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
- 19 | Ivanov
- 10 | May
- 20 | Smirnoff
- 31 | Popov
- 30 | Ford
+CREATE TABLE salespeople10_20(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople20_30(salesperson_id INT PRIMARY KEY, d2 INT, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople30_40(salesperson_id INT PRIMARY KEY, d3 DATE, salesperson_name VARCHAR(30));
+INSERT INTO salespeople10_20 VALUES ('dummy value 1', 19, 'Ivanov');
+INSERT INTO salespeople20_30 VALUES (20, 101, 'Smirnoff');
+INSERT INTO salespeople30_40 VALUES (31, now(), 'Popov');
+ALTER TABLE salespeople10_20 DROP COLUMN d1;
+ALTER TABLE salespeople20_30 DROP COLUMN d2;
+ALTER TABLE salespeople30_40 DROP COLUMN d3;
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20);
+ALTER TABLE salespeople ATTACH PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30);
+ALTER TABLE salespeople ATTACH PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
+select * from salespeople;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
+ 19 | Ivanov
+ 10 | May
+ 20 | Smirnoff
+ 31 | Popov
+ 30 | Ford
(6 rows)
-select * from salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+select * from salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-select * from salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 19 | Ivanov
- 10 | May
- 20 | Smirnoff
- 31 | Popov
- 30 | Ford
+select * from salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 19 | Ivanov
+ 10 | May
+ 20 | Smirnoff
+ 31 | Popov
+ 30 | Ford
(5 rows)
-DROP TABLE salesmans;
+DROP TABLE salespeople;
--
-- Test: merge sub-partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr2022_01_10 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-10');
CREATE TABLE sales_apr2022_10_20 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20');
CREATE TABLE sales_apr2022_20_30 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01');
@@ -482,88 +482,88 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_apr2022_01_10;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_apr2022_10_20;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 4 | Ivanov | 750 | 04-13-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 4 | Ivanov | 750 | 04-13-2022
(1 row)
SELECT * FROM sales_apr2022_20_30;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
(1 row)
ALTER TABLE sales_apr2022 MERGE PARTITIONS (sales_apr2022_01_10, sales_apr2022_10_20, sales_apr2022_20_30) INTO sales_apr_all;
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_apr_all;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
DROP TABLE sales_range;
@@ -574,8 +574,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -607,33 +607,33 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, MERGE PARTITIONS with data
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki');
CREATE TABLE sales_west PARTITION OF sales_list FOR VALUES IN ('Lisbon', 'New York', 'Madrid');
CREATE TABLE sales_east PARTITION OF sales_list FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok');
CREATE TABLE sales_central PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
-- show partitions with conditions:
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
@@ -662,68 +662,68 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
(3 rows)
SELECT * FROM sales_list;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Helsinki | 1200 | 03-06-2022
- 9 | May | Helsinki | 1200 | 03-11-2022
- 10 | Halder | Oslo | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Helsinki | 1200 | 03-06-2022
+ 9 | May | Helsinki | 1200 | 03-11-2022
+ 10 | Halder | Oslo | 800 | 03-02-2022
(14 rows)
SELECT * FROM sales_nord;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Helsinki | 1200 | 03-06-2022
- 9 | May | Helsinki | 1200 | 03-11-2022
- 10 | Halder | Oslo | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Helsinki | 1200 | 03-06-2022
+ 9 | May | Helsinki | 1200 | 03-11-2022
+ 10 | Halder | Oslo | 800 | 03-02-2022
(4 rows)
SELECT * FROM sales_all;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(10 rows)
-- Use indexscan for testing indexes after merging partitions
SET enable_seqscan = OFF;
SELECT * FROM sales_all WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
(1 row)
RESET enable_seqscan;
@@ -803,7 +803,7 @@ DROP TABLE t;
--
-- Try mixing permanent and temporary partitions.
--
-SET search_path = public, pg_temp;
+SET search_path = partitions_merge_schema, pg_temp, public;
CREATE TABLE t (i int) PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
@@ -823,16 +823,16 @@ SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
tp_1_2 | FOR VALUES FROM (1) TO (2) | p
(2 rows)
-SET search_path = pg_temp, public;
+SET search_path = pg_temp, partitions_merge_schema, public;
-- Can't merge persistent partitions into a temporary partition
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ERROR: cannot create a temporary relation as partition of permanent relation "t"
-RESET search_path;
+SET search_path = partitions_merge_schema, public;
-- Can't merge persistent partitions into a temporary partition
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
ERROR: cannot create a temporary relation as partition of permanent relation "t"
DROP TABLE t;
-SET search_path = pg_temp, public;
+SET search_path = pg_temp, partitions_merge_schema, public;
BEGIN;
CREATE TABLE t (i int) PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
@@ -853,7 +853,7 @@ SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
tp_1_2 | FOR VALUES FROM (1) TO (2) | t
(2 rows)
-SET search_path = public, pg_temp;
+SET search_path = partitions_merge_schema, pg_temp, public;
-- Can't merge temporary partitions into a persistent partition
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ERROR: cannot create a permanent relation as partition of temporary relation "t"
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 660361cd20e..ed25ff08d9f 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -10,7 +10,7 @@ SET search_path = partition_split_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id int, salesman_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -84,8 +84,8 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-01'));
ERROR: upper bound of partition "sales_apr2022" is greater than upper bound of split partition
-LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
- ^
+LINE 4: PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO...
+ ^
-- ERROR: lower bound of partition "sales_mar2022" conflicts with upper bound of previous partition "sales_feb2022"
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
(PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
@@ -109,7 +109,7 @@ DROP TABLE sales_others;
--
-- Add rows into partitioned table then split partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -132,68 +132,68 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_mar2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
DROP TABLE sales_range CASCADE;
--
-- Add split partition, then add rows into partitioned table
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -216,61 +216,61 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_mar2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
DROP TABLE sales_range CASCADE;
@@ -280,7 +280,7 @@ DROP TABLE sales_range CASCADE;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -288,103 +288,103 @@ CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month
CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1) TO (2022, 1, 1);
CREATE TABLE sales_jan_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(9 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
(4 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
ALTER TABLE sales_date SPLIT PARTITION sales_jan_feb2022 INTO
(PARTITION sales_jan2022 FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1),
PARTITION sales_feb2022 FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1));
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(11 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
(3 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
--ERROR: relation "sales_jan_feb2022" does not exist
@@ -396,7 +396,7 @@ DROP TABLE sales_date CASCADE;
--
-- Test: split DEFAULT partition; use an index on partition key; check index after split
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date);
@@ -415,19 +415,19 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 6 | Poirot | 150 | 02-11-2022
- 7 | Li | 175 | 03-08-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 11 | Trump | 380 | 04-06-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 7 | Li | 175 | 03-08-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 11 | Trump | 380 | 04-06-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(11 rows)
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema';
@@ -445,34 +445,34 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
SET enable_indexscan = ON;
SET enable_seqscan = OFF;
SELECT * FROM sales_feb2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_mar2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 11 | Trump | 380 | 04-06-2022
- 5 | Deev | 250 | 04-07-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 11 | Trump | 380 | 04-06-2022
+ 5 | Deev | 250 | 04-07-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_others where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
SET enable_indexscan = ON;
@@ -505,7 +505,7 @@ DROP TABLE sales_range CASCADE;
--
-- Test: some cases for splitting DEFAULT partition (different bounds)
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- sales_error intersects with sales_dec2022 (lower bound)
-- ERROR: lower bound of partition "sales_error" conflicts with upper bound of previous partition "sales_dec2022"
@@ -562,7 +562,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301),
PARTITION sales_others DEFAULT);
DROP TABLE sales_range;
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2022 and sales_feb2022
ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
@@ -574,20 +574,20 @@ DROP TABLE sales_range;
--
-- Test: split partition with CHECK and FOREIGN KEY CONSTRAINTs on partitioned table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE TABLE sales_range (
-salesman_id INT REFERENCES salesmans(salesman_id),
+salesperson_id INT REFERENCES salespeople(salesperson_id),
sales_amount INT CHECK (sales_amount > 1),
sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
@@ -596,50 +596,50 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
-- We should see the same CONSTRAINTs as on sales_feb_mar_apr2022 partition
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
-- ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
INSERT INTO sales_range VALUES (1, 0, '2022-03-11');
ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
DETAIL: Failing row contains (1, 0, 03-11-2022).
--- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
+-- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
INSERT INTO sales_range VALUES (-1, 10, '2022-03-11');
-ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
-DETAIL: Key (salesman_id)=(-1) is not present in table "salesmans".
+ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
+DETAIL: Key (salesperson_id)=(-1) is not present in table "salespeople".
-- ok
INSERT INTO sales_range VALUES (1, 10, '2022-03-11');
DROP TABLE sales_range CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE sales (salesman_id INT REFERENCES salesmans(salesman_id), sales_amount INT, sales_date DATE);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (19, 'Ivanov');
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-INSERT INTO salesmans VALUES (30, 'Ford');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (19, 'Ivanov');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (30, 'Ford');
INSERT INTO sales VALUES (1, 100, '2022-03-01');
INSERT INTO sales VALUES (1, 110, '2022-03-02');
INSERT INTO sales VALUES (10, 150, '2022-03-01');
@@ -648,280 +648,280 @@ INSERT INTO sales VALUES (19, 200, '2022-03-04');
INSERT INTO sales VALUES (20, 50, '2022-03-12');
INSERT INTO sales VALUES (20, 170, '2022-03-02');
INSERT INTO sales VALUES (30, 30, '2022-03-04');
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
- 20 | Smirnoff
- 30 | Ford
+SELECT * FROM salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
+ 20 | Smirnoff
+ 30 | Ford
(4 rows)
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
+SELECT * FROM salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
(2 rows)
-SELECT * FROM salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+SELECT * FROM salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-SELECT * FROM salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+SELECT * FROM salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
--- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
+-- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
INSERT INTO sales VALUES (40, 50, '2022-03-04');
-ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
-DETAIL: Key (salesman_id)=(40) is not present in table "salesmans".
+ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
+DETAIL: Key (salesperson_id)=(40) is not present in table "salespeople".
-- ok
INSERT INTO sales VALUES (30, 50, '2022-03-04');
DROP TABLE sales CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
RAISE NOTICE 'trigger(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (10, 'May');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (19, 'Ivanov');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+INSERT INTO salespeople10_40 VALUES (19, 'Ivanov');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans30_40 VALUES (30, 'Ford');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+INSERT INTO salespeople30_40 VALUES (30, 'Ford');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
+SELECT * FROM salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
(2 rows)
-SELECT * FROM salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+SELECT * FROM salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-SELECT * FROM salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+SELECT * FROM salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: split partition witch identity column
-- If split partition column is identity column, columns of new partitions are identity columns too.
--
-CREATE TABLE salesmans(salesman_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans1_2 PARTITION OF salesmans FOR VALUES FROM (1) TO (2);
+CREATE TABLE salespeople(salesperson_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople1_2 PARTITION OF salespeople FOR VALUES FROM (1) TO (2);
-- Create new partition with identity column:
-CREATE TABLE salesmans2_5(salesman_id INT NOT NULL, salesman_name VARCHAR(30));
-ALTER TABLE salesmans ATTACH PARTITION salesmans2_5 FOR VALUES FROM (2) TO (5);
-INSERT INTO salesmans (salesman_name) VALUES ('Poirot');
-INSERT INTO salesmans (salesman_name) VALUES ('Ivanov');
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+CREATE TABLE salespeople2_5(salesperson_id INT NOT NULL, salesperson_name VARCHAR(30));
+ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO (5);
+INSERT INTO salespeople (salesperson_name) VALUES ('Poirot');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-- Split partition has identity column:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_5'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-ALTER TABLE salesmans SPLIT PARTITION salesmans2_5 INTO
- (PARTITION salesmans2_3 FOR VALUES FROM (2) TO (3),
- PARTITION salesmans3_4 FOR VALUES FROM (3) TO (4),
- PARTITION salesmans4_5 FOR VALUES FROM (4) TO (5));
-INSERT INTO salesmans (salesman_name) VALUES ('May');
-INSERT INTO salesmans (salesman_name) VALUES ('Ford');
-SELECT * FROM salesmans1_2;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+ALTER TABLE salespeople SPLIT PARTITION salespeople2_5 INTO
+ (PARTITION salespeople2_3 FOR VALUES FROM (2) TO (3),
+ PARTITION salespeople3_4 FOR VALUES FROM (3) TO (4),
+ PARTITION salespeople4_5 FOR VALUES FROM (4) TO (5));
+INSERT INTO salespeople (salesperson_name) VALUES ('May');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ford');
+SELECT * FROM salespeople1_2;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans2_3;
- salesman_id | salesman_name
--------------+---------------
- 2 | Ivanov
+SELECT * FROM salespeople2_3;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 2 | Ivanov
(1 row)
-SELECT * FROM salesmans3_4;
- salesman_id | salesman_name
--------------+---------------
- 3 | May
+SELECT * FROM salespeople3_4;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 3 | May
(1 row)
-SELECT * FROM salesmans4_5;
- salesman_id | salesman_name
--------------+---------------
- 4 | Ford
+SELECT * FROM salespeople4_5;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 4 | Ford
(1 row)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-- New partitions have identity-columns:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_3'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans3_4'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans4_5'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create new partition with some deleted columns:
-CREATE TABLE salesmans10_40(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, d2 INT, d3 DATE, salesman_name VARCHAR(30));
-INSERT INTO salesmans10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
-INSERT INTO salesmans10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
-ALTER TABLE salesmans10_40 DROP COLUMN d1;
-ALTER TABLE salesmans10_40 DROP COLUMN d2;
-ALTER TABLE salesmans10_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_40 FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
-select * from salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+CREATE TABLE salespeople10_40(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, d2 INT, d3 DATE, salesperson_name VARCHAR(30));
+INSERT INTO salespeople10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
+INSERT INTO salespeople10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
+ALTER TABLE salespeople10_40 DROP COLUMN d1;
+ALTER TABLE salespeople10_40 DROP COLUMN d2;
+ALTER TABLE salespeople10_40 DROP COLUMN d3;
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_40 FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
+select * from salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-select * from salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 19 | Ivanov
- 10 | May
+select * from salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 19 | Ivanov
+ 10 | May
(2 rows)
-select * from salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+select * from salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-select * from salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+select * from salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split sub-partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_all PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -941,31 +941,31 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
ALTER TABLE sales_apr2022 SPLIT PARTITION sales_apr_all INTO
@@ -973,50 +973,50 @@ ALTER TABLE sales_apr2022 SPLIT PARTITION sales_apr_all INTO
PARTITION sales_apr2022_10_20 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20'),
PARTITION sales_apr2022_20_30 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01'));
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_apr2022_01_10;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_apr2022_10_20;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 4 | Ivanov | 750 | 04-13-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 4 | Ivanov | 750 | 04-13-2022
(1 row)
SELECT * FROM sales_apr2022_20_30;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
(1 row)
DROP TABLE sales_range;
@@ -1027,8 +1027,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -1067,8 +1067,8 @@ DROP TABLE sales_list;
-- * new partitions do not have a value that split partition has.
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -1092,109 +1092,109 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, SPLIT PARTITION with data
--
CREATE TABLE sales_list
-(salesman_id SERIAL,
- salesman_name VARCHAR(30),
+(salesperson_id SERIAL,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Helsinki', 'St. Petersburg', 'Oslo');
CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'),
PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv'));
SELECT * FROM sales_list;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Oslo | 1200 | 03-06-2022
- 9 | May | Oslo | 1200 | 03-11-2022
- 10 | Halder | Helsinki | 800 | 03-02-2022
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Oslo | 1200 | 03-06-2022
+ 9 | May | Oslo | 1200 | 03-11-2022
+ 10 | Halder | Helsinki | 800 | 03-02-2022
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
(14 rows)
SELECT * FROM sales_west;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
(4 rows)
SELECT * FROM sales_east;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
(2 rows)
SELECT * FROM sales_nord;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Oslo | 1200 | 03-06-2022
- 9 | May | Oslo | 1200 | 03-11-2022
- 10 | Halder | Helsinki | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Oslo | 1200 | 03-06-2022
+ 9 | May | Oslo | 1200 | 03-11-2022
+ 10 | Halder | Helsinki | 800 | 03-02-2022
(4 rows)
SELECT * FROM sales_central;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(4 rows)
-- Use indexscan for testing indexes after splitting partition
SET enable_indexscan = ON;
SET enable_seqscan = OFF;
SELECT * FROM sales_central WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
(1 row)
SET enable_indexscan = ON;
@@ -1205,7 +1205,7 @@ DROP TABLE sales_list;
-- * split DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
INSERT INTO sales_range VALUES (2, 'Smirnoff', 500, '2022-02-09');
@@ -1228,42 +1228,42 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
PARTITION sales_feb2022_1decade FOR VALUES FROM ('2022-02-01') TO ('2022-02-10'),
PARTITION sales_apr2022_1decade FOR VALUES FROM ('2022-04-01') TO ('2022-04-10'));
SELECT * FROM sales_jan2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 13 | Gandi | 377 | 01-09-2022
(1 row)
SELECT * FROM sales_feb2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-09-2022
- 6 | Poirot | 150 | 02-07-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-09-2022
+ 6 | Poirot | 150 | 02-07-2022
(2 rows)
SELECT * FROM sales_mar2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
(1 row)
SELECT * FROM sales_apr2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 10 | Halder | 350 | 01-28-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 10 | Halder | 350 | 01-28-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(8 rows)
DROP TABLE sales_range;
@@ -1272,7 +1272,7 @@ DROP TABLE sales_range;
-- * split non-DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -1295,42 +1295,42 @@ ALTER TABLE sales_range SPLIT PARTITION sales_all INTO
PARTITION sales_feb2022_1decade FOR VALUES FROM ('2022-02-01') TO ('2022-02-10'),
PARTITION sales_apr2022_1decade FOR VALUES FROM ('2022-04-01') TO ('2022-04-10'));
SELECT * FROM sales_jan2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 13 | Gandi | 377 | 01-09-2022
(1 row)
SELECT * FROM sales_feb2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-09-2022
- 6 | Poirot | 150 | 02-07-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-09-2022
+ 6 | Poirot | 150 | 02-07-2022
(2 rows)
SELECT * FROM sales_mar2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
(1 row)
SELECT * FROM sales_apr2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
- 1 | May | 1000 | 01-31-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 10 | Halder | 350 | 01-28-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
+ 1 | May | 1000 | 01-31-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 10 | Halder | 350 | 01-28-2022
+ 12 | Plato | 350 | 03-19-2022
(8 rows)
DROP TABLE sales_range;
@@ -1338,7 +1338,7 @@ DROP TABLE sales_range;
-- Test for split non-DEFAULT partition to DEFAULT partition + partitions
-- with spaces between bounds.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -1360,56 +1360,56 @@ ALTER TABLE sales_range SPLIT PARTITION sales_all INTO
PARTITION sales_others DEFAULT);
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(4 rows)
DROP TABLE sales_range;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 29ae6a09f13..9e21d8b163f 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -13,13 +13,13 @@ SET search_path = partitions_merge_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_dec2021 PARTITION OF sales_range FOR VALUES FROM ('2021-12-01') TO ('2021-12-31');
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-15');
CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -50,7 +50,7 @@ DROP TABLE sales_range;
--
-- Add rows into partitioned table, then merge partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -108,7 +108,7 @@ DROP TABLE sales_range;
--
-- Merge some partitions into DEFAULT partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -151,7 +151,7 @@ DROP TABLE sales_range;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -162,15 +162,15 @@ CREATE TABLE sales_jan2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1)
CREATE TABLE sales_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -180,8 +180,8 @@ SELECT * FROM sales_other;
ALTER TABLE sales_date MERGE PARTITIONS (sales_jan2022, sales_feb2022) INTO sales_jan_feb2022;
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -193,14 +193,14 @@ DROP TABLE sales_date;
--
-- Test: merge partitions of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_20 PARTITION OF salesmans FOR VALUES FROM (10) TO (20);
-CREATE TABLE salesmans20_30 PARTITION OF salesmans FOR VALUES FROM (20) TO (30);
-CREATE TABLE salesmans30_40 PARTITION OF salesmans FOR VALUES FROM (30) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_20 PARTITION OF salespeople FOR VALUES FROM (10) TO (20);
+CREATE TABLE salespeople20_30 PARTITION OF salespeople FOR VALUES FROM (20) TO (30);
+CREATE TABLE salespeople30_40 PARTITION OF salespeople FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
@@ -209,80 +209,80 @@ BEGIN
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
+INSERT INTO salespeople VALUES (10, 'May');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_20 VALUES (19, 'Ivanov');
+INSERT INTO salespeople10_20 VALUES (19, 'Ivanov');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (30, 'Ford');
+INSERT INTO salespeople10_40 VALUES (30, 'Ford');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_40;
-DROP TABLE salesmans;
+DROP TABLE salespeople;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: merge partitions with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create partitions with some deleted columns:
-CREATE TABLE salesmans10_20(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-CREATE TABLE salesmans20_30(salesman_id INT PRIMARY KEY, d2 INT, salesman_name VARCHAR(30));
-CREATE TABLE salesmans30_40(salesman_id INT PRIMARY KEY, d3 DATE, salesman_name VARCHAR(30));
+CREATE TABLE salespeople10_20(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople20_30(salesperson_id INT PRIMARY KEY, d2 INT, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople30_40(salesperson_id INT PRIMARY KEY, d3 DATE, salesperson_name VARCHAR(30));
-INSERT INTO salesmans10_20 VALUES ('dummy value 1', 19, 'Ivanov');
-INSERT INTO salesmans20_30 VALUES (20, 101, 'Smirnoff');
-INSERT INTO salesmans30_40 VALUES (31, now(), 'Popov');
+INSERT INTO salespeople10_20 VALUES ('dummy value 1', 19, 'Ivanov');
+INSERT INTO salespeople20_30 VALUES (20, 101, 'Smirnoff');
+INSERT INTO salespeople30_40 VALUES (31, now(), 'Popov');
-ALTER TABLE salesmans10_20 DROP COLUMN d1;
-ALTER TABLE salesmans20_30 DROP COLUMN d2;
-ALTER TABLE salesmans30_40 DROP COLUMN d3;
+ALTER TABLE salespeople10_20 DROP COLUMN d1;
+ALTER TABLE salespeople20_30 DROP COLUMN d2;
+ALTER TABLE salespeople30_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20);
-ALTER TABLE salesmans ATTACH PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30);
-ALTER TABLE salesmans ATTACH PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40);
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20);
+ALTER TABLE salespeople ATTACH PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30);
+ALTER TABLE salespeople ATTACH PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-select * from salesmans;
-select * from salesmans01_10;
-select * from salesmans10_40;
+select * from salespeople;
+select * from salespeople01_10;
+select * from salespeople10_40;
-DROP TABLE salesmans;
+DROP TABLE salespeople;
--
-- Test: merge sub-partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr2022_01_10 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-10');
CREATE TABLE sales_apr2022_10_20 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20');
CREATE TABLE sales_apr2022_20_30 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01');
@@ -329,8 +329,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -366,14 +366,14 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, MERGE PARTITIONS with data
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki');
@@ -382,20 +382,20 @@ CREATE TABLE sales_east PARTITION OF sales_list FOR VALUES IN ('Bejing', 'Delhi'
CREATE TABLE sales_central PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
-- show partitions with conditions:
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
@@ -420,7 +420,7 @@ SET enable_seqscan = OFF;
SELECT * FROM sales_all WHERE sales_state = 'Warsaw';
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
RESET enable_seqscan;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 576f9f0f63c..4228917654c 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -13,7 +13,7 @@ SET search_path = partition_split_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id int, salesman_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -98,7 +98,7 @@ DROP TABLE sales_others;
--
-- Add rows into partitioned table then split partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -135,7 +135,7 @@ DROP TABLE sales_range CASCADE;
--
-- Add split partition, then add rows into partitioned table
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -175,7 +175,7 @@ DROP TABLE sales_range CASCADE;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -185,15 +185,15 @@ CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1)
CREATE TABLE sales_jan_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -204,8 +204,8 @@ ALTER TABLE sales_date SPLIT PARTITION sales_jan_feb2022 INTO
(PARTITION sales_jan2022 FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1),
PARTITION sales_feb2022 FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1));
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -221,7 +221,7 @@ DROP TABLE sales_date CASCADE;
--
-- Test: split DEFAULT partition; use an index on partition key; check index after split
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date);
@@ -272,7 +272,7 @@ DROP TABLE sales_range CASCADE;
--
-- Test: some cases for splitting DEFAULT partition (different bounds)
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- sales_error intersects with sales_dec2022 (lower bound)
@@ -322,7 +322,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
DROP TABLE sales_range;
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2022 and sales_feb2022
@@ -337,11 +337,11 @@ DROP TABLE sales_range;
--
-- Test: split partition with CHECK and FOREIGN KEY CONSTRAINTs on partitioned table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE TABLE sales_range (
-salesman_id INT REFERENCES salesmans(salesman_id),
+salesperson_id INT REFERENCES salespeople(salesperson_id),
sales_amount INT CHECK (sales_amount > 1),
sales_date DATE) PARTITION BY RANGE (sales_date);
@@ -363,28 +363,28 @@ SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conre
-- ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
INSERT INTO sales_range VALUES (1, 0, '2022-03-11');
--- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
+-- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
INSERT INTO sales_range VALUES (-1, 10, '2022-03-11');
-- ok
INSERT INTO sales_range VALUES (1, 10, '2022-03-11');
DROP TABLE sales_range CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE sales (salesman_id INT REFERENCES salesmans(salesman_id), sales_amount INT, sales_date DATE);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (19, 'Ivanov');
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (19, 'Ivanov');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (30, 'Ford');
INSERT INTO sales VALUES (1, 100, '2022-03-01');
INSERT INTO sales VALUES (1, 110, '2022-03-02');
@@ -395,36 +395,36 @@ INSERT INTO sales VALUES (20, 50, '2022-03-12');
INSERT INTO sales VALUES (20, 170, '2022-03-02');
INSERT INTO sales VALUES (30, 30, '2022-03-04');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_40;
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_20;
-SELECT * FROM salesmans20_30;
-SELECT * FROM salesmans30_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_20;
+SELECT * FROM salespeople20_30;
+SELECT * FROM salespeople30_40;
--- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
+-- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
INSERT INTO sales VALUES (40, 50, '2022-03-04');
-- ok
INSERT INTO sales VALUES (30, 50, '2022-03-04');
DROP TABLE sales CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
@@ -433,125 +433,125 @@ BEGIN
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
+INSERT INTO salespeople VALUES (10, 'May');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (19, 'Ivanov');
+INSERT INTO salespeople10_40 VALUES (19, 'Ivanov');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans30_40 VALUES (30, 'Ford');
+INSERT INTO salespeople30_40 VALUES (30, 'Ford');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_20;
-SELECT * FROM salesmans20_30;
-SELECT * FROM salesmans30_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_20;
+SELECT * FROM salespeople20_30;
+SELECT * FROM salespeople30_40;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: split partition witch identity column
-- If split partition column is identity column, columns of new partitions are identity columns too.
--
-CREATE TABLE salesmans(salesman_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans1_2 PARTITION OF salesmans FOR VALUES FROM (1) TO (2);
+CREATE TABLE salespeople1_2 PARTITION OF salespeople FOR VALUES FROM (1) TO (2);
-- Create new partition with identity column:
-CREATE TABLE salesmans2_5(salesman_id INT NOT NULL, salesman_name VARCHAR(30));
-ALTER TABLE salesmans ATTACH PARTITION salesmans2_5 FOR VALUES FROM (2) TO (5);
+CREATE TABLE salespeople2_5(salesperson_id INT NOT NULL, salesperson_name VARCHAR(30));
+ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO (5);
-INSERT INTO salesmans (salesman_name) VALUES ('Poirot');
-INSERT INTO salesmans (salesman_name) VALUES ('Ivanov');
+INSERT INTO salespeople (salesperson_name) VALUES ('Poirot');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
-- Split partition has identity column:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_5'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
-ALTER TABLE salesmans SPLIT PARTITION salesmans2_5 INTO
- (PARTITION salesmans2_3 FOR VALUES FROM (2) TO (3),
- PARTITION salesmans3_4 FOR VALUES FROM (3) TO (4),
- PARTITION salesmans4_5 FOR VALUES FROM (4) TO (5));
+ALTER TABLE salespeople SPLIT PARTITION salespeople2_5 INTO
+ (PARTITION salespeople2_3 FOR VALUES FROM (2) TO (3),
+ PARTITION salespeople3_4 FOR VALUES FROM (3) TO (4),
+ PARTITION salespeople4_5 FOR VALUES FROM (4) TO (5));
-INSERT INTO salesmans (salesman_name) VALUES ('May');
-INSERT INTO salesmans (salesman_name) VALUES ('Ford');
+INSERT INTO salespeople (salesperson_name) VALUES ('May');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ford');
-SELECT * FROM salesmans1_2;
-SELECT * FROM salesmans2_3;
-SELECT * FROM salesmans3_4;
-SELECT * FROM salesmans4_5;
+SELECT * FROM salespeople1_2;
+SELECT * FROM salespeople2_3;
+SELECT * FROM salespeople3_4;
+SELECT * FROM salespeople4_5;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
-- New partitions have identity-columns:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_3'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans3_4'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans4_5'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create new partition with some deleted columns:
-CREATE TABLE salesmans10_40(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, d2 INT, d3 DATE, salesman_name VARCHAR(30));
+CREATE TABLE salespeople10_40(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, d2 INT, d3 DATE, salesperson_name VARCHAR(30));
-INSERT INTO salesmans10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
-INSERT INTO salesmans10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
+INSERT INTO salespeople10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
+INSERT INTO salespeople10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
-ALTER TABLE salesmans10_40 DROP COLUMN d1;
-ALTER TABLE salesmans10_40 DROP COLUMN d2;
-ALTER TABLE salesmans10_40 DROP COLUMN d3;
+ALTER TABLE salespeople10_40 DROP COLUMN d1;
+ALTER TABLE salespeople10_40 DROP COLUMN d2;
+ALTER TABLE salespeople10_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_40 FOR VALUES FROM (10) TO (40);
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_40 FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-select * from salesmans01_10;
-select * from salesmans10_20;
-select * from salesmans20_30;
-select * from salesmans30_40;
+select * from salespeople01_10;
+select * from salespeople10_20;
+select * from salespeople20_30;
+select * from salespeople30_40;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split sub-partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_all PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -598,8 +598,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -635,8 +635,8 @@ DROP TABLE sales_list;
-- * new partitions do not have a value that split partition has.
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -663,34 +663,34 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, SPLIT PARTITION with data
--
CREATE TABLE sales_list
-(salesman_id SERIAL,
- salesman_name VARCHAR(30),
+(salesperson_id SERIAL,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Helsinki', 'St. Petersburg', 'Oslo');
CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
@@ -709,7 +709,7 @@ SET enable_seqscan = OFF;
SELECT * FROM sales_central WHERE sales_state = 'Warsaw';
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
SET enable_indexscan = ON;
SET enable_seqscan = ON;
@@ -721,7 +721,7 @@ DROP TABLE sales_list;
-- * split DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -759,7 +759,7 @@ DROP TABLE sales_range;
-- * split non-DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -796,7 +796,7 @@ DROP TABLE sales_range;
-- Test for split non-DEFAULT partition to DEFAULT partition + partitions
-- with spaces between bounds.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
--
2.39.3 (Apple Git-145)
[application/octet-stream] v7-0006-ALTER-TABLE-.-MERGE-PARTITIONS-.-make-inherit-par.patch (1.6K, ../CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com/7-v7-0006-ALTER-TABLE-.-MERGE-PARTITIONS-.-make-inherit-par.patch)
download | inline diff:
From 11f4b57db9e381745a107d3f49e796afd5e8924e Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Fri, 19 Apr 2024 14:55:24 +0300
Subject: [PATCH v7 6/7] ALTER TABLE ... MERGE PARTITIONS ... make inherit
parent's am
Reported-by:
Bug:
Discussion:
Author:
Reviewed-by:
Tested-by:
Backpatch-through:
---
src/backend/commands/tablecmds.c | 6 ++++++
1 file changed, 6 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2025e68bdae..575e99509d8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21186,6 +21186,11 @@ moveSplitTableRows(Relation rel, Relation splitRel, List *partlist, List *newPar
*
* Emulates command: CREATE [TEMP] TABLE <newPartName> (LIKE <modelRel's name>
* INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)
+ *
+ * Also, this function sets the new partition access method same as parent
+ * table access methods (similarly to CREATE TABLE ... PARTITION OF). It
+ * checks that parent and child tables have compatible persistence.
+ *
* Function returns the created relation (locked in AccessExclusiveLock mode).
*/
static Relation
@@ -21213,6 +21218,7 @@ createPartitionTable(RangeVar *newPartName, Relation modelRel,
createStmt->oncommit = ONCOMMIT_NOOP;
createStmt->tablespacename = NULL;
createStmt->if_not_exists = false;
+ createStmt->accessMethod = get_am_name(modelRel->rd_rel->relam);
tlc = makeNode(TableLikeClause);
tlc->relation = makeRangeVar(get_namespace_name(RelationGetNamespace(modelRel)),
--
2.39.3 (Apple Git-145)
[application/octet-stream] v7-0007-doc-review-for-ALTER-TABLE-.-SPLIT-MERGE-PARTITIO.patch (6.0K, ../CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com/8-v7-0007-doc-review-for-ALTER-TABLE-.-SPLIT-MERGE-PARTITIO.patch)
download | inline diff:
From 9be4d11f85fd4adac924b40eded963e74bb55171 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
Date: Thu, 18 Apr 2024 15:40:44 -0500
Subject: [PATCH v7 7/7] doc review for ALTER TABLE ... SPLIT/MERGE PARTITION
---
doc/src/sgml/ddl.sgml | 4 ++--
doc/src/sgml/ref/alter_table.sgml | 22 +++++++++++-----------
2 files changed, 13 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 026bfff70f3..01277b1d327 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4384,7 +4384,7 @@ ALTER INDEX measurement_city_id_logdate_key
<para>
There is also an option for merging multiple table partitions into
- a single partition using the
+ a single partition using
<link linkend="sql-altertable-merge-partitions"><command>ALTER TABLE ... MERGE PARTITIONS</command></link>.
This feature simplifies the management of partitioned tables by allowing
users to combine partitions that are no longer needed as
@@ -4403,7 +4403,7 @@ ALTER TABLE measurement
<para>
Similarly to merging multiple table partitions, there is an option for
- splitting a single partition into multiple using the
+ splitting a single partition into multiple partitions using
<link linkend="sql-altertable-split-partition"><command>ALTER TABLE ... SPLIT PARTITION</command></link>.
This feature could come in handy when one partition grows too big
and needs to be split into multiple. It's important to note that
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index fe36ff82e52..e52cfee840c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1136,16 +1136,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If the split partition is a DEFAULT partition, one of the new partitions must be DEFAULT.
In case one of the new partitions or one of existing partitions is DEFAULT,
new partitions <replaceable class="parameter">partition_name1</replaceable>,
- <replaceable class="parameter">partition_name2</replaceable>, ... can have spaces
+ <replaceable class="parameter">partition_name2</replaceable>, ... can have gaps
between partitions bounds. If the partitioned table does not have a DEFAULT
partition, the DEFAULT partition can be defined as one of the new partitions.
</para>
<para>
In case new partitions do not contain a DEFAULT partition and the partitioned table
- does not have a DEFAULT partition, the following must be true: sum bounds of
+ does not have a DEFAULT partition, the following must be true: the sum bounds of
new partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>, ... should be
- equal to bound of split partition <replaceable class="parameter">partition_name</replaceable>.
+ equal to the bounds of split partition <replaceable class="parameter">partition_name</replaceable>.
One of the new partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>, ... can have
the same name as split partition <replaceable class="parameter">partition_name</replaceable>
@@ -1168,24 +1168,24 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
- This form merges several partitions into the one partition of the target table.
- Hash-partitioning is not supported. If DEFAULT partition is not in the
+ This form merges several partitions of the target table into a single partition.
+ Hash-partitioning is not supported. If a DEFAULT partition is not in the
list of partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...]:
<itemizedlist>
<listitem>
<para>
- For range-partitioned tables it is necessary that the ranges
+ For range-partitioned tables, it is necessary that the ranges
of the partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...] can
- be merged into one range without spaces and overlaps (otherwise an error
+ be merged into one range with neither gaps nor overlaps (otherwise an error
will be generated). The combined range will be the range for the partition
<replaceable class="parameter">partition_name</replaceable>.
</para>
</listitem>
<listitem>
<para>
- For list-partitioned tables the value lists of all partitions
+ For list-partitioned tables, the value lists of all partitions
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...] are
combined and form the list of values of partition
@@ -1193,7 +1193,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
</listitem>
</itemizedlist>
- If DEFAULT partition is in the list of partitions <replaceable class="parameter">partition_name1</replaceable>,
+ If a DEFAULT partition is in the list of partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...]:
<itemizedlist>
<listitem>
@@ -1204,8 +1204,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
<listitem>
<para>
- For range- and list-partitioned tables the ranges and lists of values
- of the merged partitions can be any.
+ For range- and list-partitioned tables, the ranges and lists of values
+ of the merged partitions can be anything.
</para>
</listitem>
</itemizedlist>
--
2.39.3 (Apple Git-145)
view thread (48+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
In-Reply-To: <CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox