From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: 李杰(慎追) <adger.lj@alibaba-inc.com>
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Cc: 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Subject: Re: 回复：how to create index concurrently on partitioned table
Date: Mon, 7 Sep 2020 21:39:16 -0500
Message-ID: <20200908023915.GD7158@telsasoft.com> (raw)
In-Reply-To: <20200901055158.GH3511@paquier.xyz>
References: <20200618024114.GF118592@paquier.xyz>
<20200616010221.GA2353@paquier.xyz>
<20200615123742.GL52676@paquier.xyz>
<20200808063744.GG20473@telsasoft.com>
<20200809050009.GA17986@paquier.xyz>
<20200809234423.GH20473@telsasoft.com>
<20200901055158.GH3511@paquier.xyz>
Thanks for completing and pushing the REINDEX patch and others.
Here's a rebasified + fixed version of the others.
On Tue, Sep 01, 2020 at 02:51:58PM +0900, Michael Paquier wrote:
> The REINDEX patch is progressing its way, so I have looked a bit at
> the part for CIC.
>
> Visibly, the case of multiple partition layers is not handled
> correctly. Here is a sequence that gets broken:
..
> This fails as follows:
> ERROR: XX000: unrecognized node type: 2139062143
> LOCATION: copyObjectImpl, copyfuncs.c:5718
Because copyObject needed to be called within a longlived context.
Also, my previous revision failed to implement your suggestion to first build
catalog entries with INVALID indexes and to then reindex them. Fixed.
--
Justin
Attachments:download all (2 files, .tar.gz)v6-0001-Allow-CREATE-INDEX-CONCURRENTLY-on-partitioned-ta.patch (text/x-diff, 13.0K) download
From 17a14e5ad128024b05ae288a5096148b3f114c98 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 6 Jun 2020 17:42:23 -0500
Subject: [PATCH v6 1/2] Allow CREATE INDEX CONCURRENTLY on partitioned table
Note, this effectively reverts 050098b14, so take care to not reintroduce the
bug it fixed.
---
doc/src/sgml/ref/create_index.sgml | 9 --
src/backend/commands/indexcmds.c | 118 ++++++++++++++++++++-----
src/test/regress/expected/indexing.out | 60 ++++++++++++-
src/test/regress/sql/indexing.sql | 18 +++-
4 files changed, 165 insertions(+), 40 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlindex 33aa64e81d..c780dc9547 100644--- a/doc/src/sgml/ref/create_index.sgml+++ b/doc/src/sgml/ref/create_index.sgml@@ -657,15 +657,6 @@ Indexes:
cannot.
</para>
- <para>- Concurrent builds for indexes on partitioned tables are currently not- supported. However, you may concurrently build the index on each- partition individually and then finally create the partitioned index- non-concurrently in order to reduce the time where writes to the- partitioned table will be locked out. In this case, building the- partitioned index is a metadata only operation.- </para>-
</refsect2>
</refsect1>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex f1b5f87e6a..61d0c4914c 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -665,17 +665,6 @@ DefineIndex(Oid relationId,
partitioned = rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE;
if (partitioned)
{
- /*- * Note: we check 'stmt->concurrent' rather than 'concurrent', so that- * the error is thrown also for temporary tables. Seems better to be- * consistent, even though we could do it on temporary table because- * we're not actually doing it concurrently.- */- if (stmt->concurrent)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("cannot create index on partitioned table \"%s\" concurrently",- RelationGetRelationName(rel))));
if (stmt->excludeOpNames)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -1110,6 +1099,11 @@ DefineIndex(Oid relationId,
if (pd->nparts != 0)
flags |= INDEX_CREATE_INVALID;
}
+ else if (concurrent && OidIsValid(parentIndexId))+ {+ /* If concurrent, initial build of index partitions as "invalid" */+ flags |= INDEX_CREATE_INVALID;+ }
if (stmt->deferrable)
constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE;
@@ -1162,6 +1156,14 @@ DefineIndex(Oid relationId,
*/
if (!stmt->relation || stmt->relation->inh)
{
+ /*+ * Need to close the relation before recursing into children, so+ * copy needed data into a longlived context.+ */++ MemoryContext ind_context = AllocSetContextCreate(PortalContext, "CREATE INDEX",+ ALLOCSET_DEFAULT_SIZES);+ MemoryContext oldcontext = MemoryContextSwitchTo(ind_context);
PartitionDesc partdesc = RelationGetPartitionDesc(rel);
int nparts = partdesc->nparts;
Oid *part_oids = palloc(sizeof(Oid) * nparts);
@@ -1173,8 +1175,10 @@ DefineIndex(Oid relationId,
nparts);
memcpy(part_oids, partdesc->oids, sizeof(Oid) * nparts);
+ parentDesc = CreateTupleDescCopy(RelationGetDescr(rel));+ table_close(rel, NoLock);+ MemoryContextSwitchTo(oldcontext);- parentDesc = RelationGetDescr(rel);
opfamOids = palloc(sizeof(Oid) * numberOfKeyAttributes);
for (i = 0; i < numberOfKeyAttributes; i++)
opfamOids[i] = get_opclass_family(classObjectId[i]);
@@ -1217,10 +1221,12 @@ DefineIndex(Oid relationId,
continue;
}
+ oldcontext = MemoryContextSwitchTo(ind_context);
childidxs = RelationGetIndexList(childrel);
attmap =
build_attrmap_by_name(RelationGetDescr(childrel),
parentDesc);
+ MemoryContextSwitchTo(oldcontext);
foreach(cell, childidxs)
{
@@ -1291,10 +1297,14 @@ DefineIndex(Oid relationId,
*/
if (!found)
{
- IndexStmt *childStmt = copyObject(stmt);+ IndexStmt *childStmt;
bool found_whole_row;
ListCell *lc;
+ oldcontext = MemoryContextSwitchTo(ind_context);+ childStmt = copyObject(stmt);+ MemoryContextSwitchTo(oldcontext);+
/*
* We can't use the same index name for the child index,
* so clear idxname to let the recursive invocation choose
@@ -1306,8 +1316,8 @@ DefineIndex(Oid relationId,
childStmt->relation = NULL;
childStmt->indexOid = InvalidOid;
childStmt->oldNode = InvalidOid;
- childStmt->oldCreateSubid = InvalidSubTransactionId;- childStmt->oldFirstRelfilenodeSubid = InvalidSubTransactionId;+ // childStmt->oldCreateSubid = InvalidSubTransactionId;+ // childStmt->oldFirstRelfilenodeSubid = InvalidSubTransactionId;
/*
* Adjust any Vars (both in expressions and in the index's
@@ -1346,10 +1356,18 @@ DefineIndex(Oid relationId,
createdConstraintId,
is_alter_table, check_rights, check_not_in_use,
skip_build, quiet);
+ if (concurrent)+ {+ PopActiveSnapshot();+ PushActiveSnapshot(GetTransactionSnapshot());+ invalidate_parent = true;+ }
}
- pgstat_progress_update_param(PROGRESS_CREATEIDX_PARTITIONS_DONE,- i + 1);+ /* For concurrent build, this is a catalog-only stage */+ if (!concurrent)+ pgstat_progress_update_param(PROGRESS_CREATEIDX_PARTITIONS_DONE,+ i + 1);
free_attrmap(attmap);
}
@@ -1376,34 +1394,86 @@ DefineIndex(Oid relationId,
table_close(pg_index, RowExclusiveLock);
heap_freetuple(newtup);
}
+ } else+ table_close(rel, NoLock);++ if (concurrent)+ {+ List *childs;+ ListCell *lc;+ int npart = 0;++ /* Reindex invalid child indexes created earlier */+ MemoryContext ind_context = AllocSetContextCreate(PortalContext, "CREATE INDEX",+ ALLOCSET_DEFAULT_SIZES);+ MemoryContext oldcontext = MemoryContextSwitchTo(ind_context);+ childs = find_inheritance_children(indexRelationId, NoLock);+ MemoryContextSwitchTo(oldcontext);++ /* Make the catalog changes visible to get_partition_parent */+ PopActiveSnapshot();+ CommandCounterIncrement();++ foreach (lc, childs)+ {+ Oid indexrelid = lfirst_oid(lc);++ if (!OidIsValid(parentIndexId))+ pgstat_progress_update_param(PROGRESS_CREATEIDX_PARTITIONS_DONE,+ npart++);++ CommandCounterIncrement();+ PushActiveSnapshot(GetTransactionSnapshot());++ if (get_index_isvalid(indexrelid))+ {+ PopActiveSnapshot();+ continue;+ }++ /* This may be a partitioned index, which is fine too */+ ReindexRelationConcurrently(indexrelid, 0);+ }++ /*+ * CIC needs to mark a partitioned index as VALID, which itself+ * requires setting READY, which is unset for CIC (even though+ * it's meaningless for an index without storage).+ */+ CommitTransactionCommand();+ StartTransactionCommand();+ PushActiveSnapshot(GetTransactionSnapshot());+ index_set_state_flags(indexRelationId, INDEX_CREATE_SET_READY);+ CommandCounterIncrement();+ index_set_state_flags(indexRelationId, INDEX_CREATE_SET_VALID);
}
/*
* Indexes on partitioned tables are not themselves built, so we're
* done here.
*/
- table_close(rel, NoLock);
if (!OidIsValid(parentIndexId))
pgstat_progress_end_command();
return address;
}
- if (!concurrent)+ table_close(rel, NoLock);+ if (!concurrent || OidIsValid(parentIndexId))
{
- /* Close the heap and we're done, in the non-concurrent case */- table_close(rel, NoLock);+ /*+ * We're done if this is the top-level index,+ * or the catalog-only phase of a partition built concurrently+ */- /* If this is the top-level index, we're done. */
if (!OidIsValid(parentIndexId))
pgstat_progress_end_command();
return address;
}
- /* save lockrelid and locktag for below, then close rel */+ /* save lockrelid and locktag for below */
heaprelid = rel->rd_lockInfo.lockRelId;
SET_LOCKTAG_RELATION(heaplocktag, heaprelid.dbId, heaprelid.relId);
- table_close(rel, NoLock);
/*
* For a concurrent build, it's important to make the catalog entries
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.outindex 7e78a07af8..f6eba3dd40 100644--- a/src/test/regress/expected/indexing.out+++ b/src/test/regress/expected/indexing.out@@ -50,11 +50,63 @@ select relname, relkind, relhassubclass, inhparent::regclass
(8 rows)
drop table idxpart;
--- Some unsupported features+-- CIC on partitioned table
create table idxpart (a int, b int, c text) partition by range (a);
-create table idxpart1 partition of idxpart for values from (0) to (10);-create index concurrently on idxpart (a);-ERROR: cannot create index on partitioned table "idxpart" concurrently+create table idxpart1 partition of idxpart for values from (0) to (10) partition by range(a);+create table idxpart11 partition of idxpart1 for values from (0) to (10) partition by range(a);+create table idxpart111 partition of idxpart11 default partition by range(a);+create table idxpart1111 partition of idxpart111 default partition by range(a);+create table idxpart2 partition of idxpart for values from (10) to (20);+insert into idxpart2 values(10),(10); -- not unique+create index concurrently on idxpart (a); -- partitioned+create index concurrently on idxpart1 (a); -- partitioned and partition+create index concurrently on idxpart11 (a); -- partitioned and partition, with no leaves+create index concurrently on idxpart2 (a); -- leaf+create unique index concurrently on idxpart (a); -- partitioned, unique failure+ERROR: could not create unique index "idxpart2_a_idx2_ccnew"+DETAIL: Key (a)=(10) is duplicated.+\d idxpart+ Partitioned table "public.idxpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | | + b | integer | | | + c | text | | | +Partition key: RANGE (a)+Indexes:+ "idxpart_a_idx" btree (a)+ "idxpart_a_idx1" UNIQUE, btree (a) INVALID+Number of partitions: 2 (Use \d+ to list them.)++\d idxpart1+ Partitioned table "public.idxpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | | + b | integer | | | + c | text | | | +Partition of: idxpart FOR VALUES FROM (0) TO (10)+Partition key: RANGE (a)+Indexes:+ "idxpart1_a_idx" btree (a)+ "idxpart1_a_idx1" btree (a)+ "idxpart1_a_idx2" UNIQUE, btree (a)+Number of partitions: 1 (Use \d+ to list them.)++\d idxpart2+ Table "public.idxpart2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | | + b | integer | | | + c | text | | | +Partition of: idxpart FOR VALUES FROM (10) TO (20)+Indexes:+ "idxpart2_a_idx" btree (a)+ "idxpart2_a_idx1" btree (a)+ "idxpart2_a_idx2" UNIQUE, btree (a) INVALID+ "idxpart2_a_idx2_ccnew" UNIQUE, btree (a) INVALID+
drop table idxpart;
-- Verify bugfix with query on indexed partitioned table with no partitions
-- https://postgr.es/m/20180124162006.pmapfiznhgngwtjf@alvherre.pgsql
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sqlindex 42f398b67c..3d4b6e9bc9 100644--- a/src/test/regress/sql/indexing.sql+++ b/src/test/regress/sql/indexing.sql@@ -29,10 +29,22 @@ select relname, relkind, relhassubclass, inhparent::regclass
where relname like 'idxpart%' order by relname;
drop table idxpart;
--- Some unsupported features+-- CIC on partitioned table
create table idxpart (a int, b int, c text) partition by range (a);
-create table idxpart1 partition of idxpart for values from (0) to (10);-create index concurrently on idxpart (a);+create table idxpart1 partition of idxpart for values from (0) to (10) partition by range(a);+create table idxpart11 partition of idxpart1 for values from (0) to (10) partition by range(a);+create table idxpart111 partition of idxpart11 default partition by range(a);+create table idxpart1111 partition of idxpart111 default partition by range(a);+create table idxpart2 partition of idxpart for values from (10) to (20);+insert into idxpart2 values(10),(10); -- not unique+create index concurrently on idxpart (a); -- partitioned+create index concurrently on idxpart1 (a); -- partitioned and partition+create index concurrently on idxpart11 (a); -- partitioned and partition, with no leaves+create index concurrently on idxpart2 (a); -- leaf+create unique index concurrently on idxpart (a); -- partitioned, unique failure+\d idxpart+\d idxpart1+\d idxpart2
drop table idxpart;
-- Verify bugfix with query on indexed partitioned table with no partitions
--
2.17.0
v6-0002-Implement-CLUSTER-of-partitioned-table.patch (text/x-diff, 10.3K) download
From c9c4de2549dff93bf16aaa70ed8342aa338d3dfe Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 7 Jun 2020 16:58:42 -0500
Subject: [PATCH v6 2/2] Implement CLUSTER of partitioned table..
This requires either specification of a partitioned index on which to cluster,
or that an partitioned index was previously set clustered.
XXX: should mark_index_clustered() SET WITHOUT CLUSTER for any parent indexes
of an index partition which were previously-clustered ?
---
src/backend/commands/cluster.c | 141 +++++++++++++++++++-------
src/bin/psql/tab-complete.c | 1 +
src/test/regress/expected/cluster.out | 23 ++++-
src/test/regress/sql/cluster.sql | 12 ++-
4 files changed, 134 insertions(+), 43 deletions(-)
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.cindex 0d647e912c..6ead8ffc79 100644--- a/src/backend/commands/cluster.c+++ b/src/backend/commands/cluster.c@@ -33,6 +33,7 @@
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_inherits.h"
#include "catalog/toasting.h"
#include "commands/cluster.h"
#include "commands/progress.h"
@@ -75,6 +76,9 @@ static void copy_table_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex,
TransactionId *pFreezeXid,
MultiXactId *pCutoffMulti);
static List *get_tables_to_cluster(MemoryContext cluster_context);
+static List *get_tables_to_cluster_partitioned(MemoryContext cluster_context,+ Oid indexOid);+static void cluster_multiple_rels(List *rvs, bool isTopLevel, int options);
/*---------------------------------------------------------------------------
@@ -127,14 +131,6 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot cluster temporary tables of other sessions")));
- /*- * Reject clustering a partitioned table.- */- if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("cannot cluster a partitioned table")));-
if (stmt->indexname == NULL)
{
ListCell *index;
@@ -172,8 +168,37 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
/* close relation, keep lock till commit */
table_close(rel, NoLock);
- /* Do the job. */- cluster_rel(tableOid, indexOid, stmt->options, isTopLevel);+ if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)+ {+ /* Do the job. */+ cluster_rel(tableOid, indexOid, stmt->options, isTopLevel);+ } else {+ List *rvs;+ MemoryContext cluster_context;++ /* Check index directly since cluster_rel isn't called for partitioned table */+ check_index_is_clusterable(rel, indexOid, true, AccessExclusiveLock);++ /* Refuse to hold strong locks in a user transaction */+ PreventInTransactionBlock(isTopLevel, "CLUSTER");++ cluster_context = AllocSetContextCreate(PortalContext,+ "Cluster",+ ALLOCSET_DEFAULT_SIZES);++ rvs = get_tables_to_cluster_partitioned(cluster_context, indexOid);+ cluster_multiple_rels(rvs, isTopLevel, stmt->options);++ /* Start a new transaction for the cleanup work. */+ StartTransactionCommand();++ rel = table_open(tableOid, ShareUpdateExclusiveLock);+ mark_index_clustered(rel, indexOid, true);+ table_close(rel, NoLock);++ /* Clean up working storage */+ MemoryContextDelete(cluster_context);+ }
}
else
{
@@ -183,7 +208,6 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
*/
MemoryContext cluster_context;
List *rvs;
- ListCell *rv;
/*
* We cannot run this form of CLUSTER inside a user transaction block;
@@ -207,26 +231,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
*/
rvs = get_tables_to_cluster(cluster_context);
- /* Commit to get out of starting transaction */- PopActiveSnapshot();- CommitTransactionCommand();-- /* Ok, now that we've got them all, cluster them one by one */- foreach(rv, rvs)- {- RelToCluster *rvtc = (RelToCluster *) lfirst(rv);-- /* Start a new transaction for each relation. */- StartTransactionCommand();- /* functions in indexes may want a snapshot set */- PushActiveSnapshot(GetTransactionSnapshot());- /* Do the job. */- cluster_rel(rvtc->tableOid, rvtc->indexOid,- stmt->options | CLUOPT_RECHECK,- isTopLevel);- PopActiveSnapshot();- CommitTransactionCommand();- }+ cluster_multiple_rels(rvs, isTopLevel, stmt->options);
/* Start a new transaction for the cleanup work. */
StartTransactionCommand();
@@ -487,12 +492,6 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal)
Relation pg_index;
ListCell *index;
- /* Disallow applying to a partitioned table */- if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("cannot mark index clustered in partitioned table")));-
/*
* If the index is already marked clustered, no need to do anything.
*/
@@ -1563,3 +1562,71 @@ get_tables_to_cluster(MemoryContext cluster_context)
return rvs;
}
++/*+ * Return a List of tables and associated index, where each index is a+ * partition of the given index+ */+static List *+get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid)+{+ List *inhoids;+ ListCell *lc;+ List *rvs = NIL;++ MemoryContext old_context = MemoryContextSwitchTo(cluster_context);++ inhoids = find_all_inheritors(indexOid, NoLock, NULL);+ foreach(lc, inhoids)+ {+ Oid indexrelid = lfirst_oid(lc);+ Oid relid = IndexGetRelation(indexrelid, false);+ RelToCluster *rvtc;++ /*+ * We have a full list of direct and indirect children, so skip+ * partitioned tables and just handle their children.+ */+ if (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE)+ continue;++ rvtc = (RelToCluster *) palloc(sizeof(RelToCluster));+ rvtc->tableOid = relid;+ rvtc->indexOid = indexrelid;+ rvs = lappend(rvs, rvtc);+ }++ MemoryContextSwitchTo(old_context);+ return rvs;+}++/* Cluster each relation in a separate transaction */+static void+cluster_multiple_rels(List *rvs, bool isTopLevel, int options)+{+ ListCell *lc;++ /* Commit to get out of starting transaction */+ PopActiveSnapshot();+ CommitTransactionCommand();++ /* Ok, now that we've got them all, cluster them one by one */+ foreach(lc, rvs)+ {+ RelToCluster *rvtc = (RelToCluster *) lfirst(lc);++ /* Start a new transaction for each relation. */+ StartTransactionCommand();++ /* functions in indexes may want a snapshot set */+ PushActiveSnapshot(GetTransactionSnapshot());++ /* Do the job. */+ cluster_rel(rvtc->tableOid, rvtc->indexOid,+ options | CLUOPT_RECHECK,+ isTopLevel);++ PopActiveSnapshot();+ CommitTransactionCommand();+ }+}diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.cindex f41785f11c..49a2648991 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -587,6 +587,7 @@ static const SchemaQuery Query_for_list_of_clusterables = {
.catname = "pg_catalog.pg_class c",
.selcondition =
"c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
CppAsString2(RELKIND_MATVIEW) ")",
.viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
.namespace = "c.relnamespace",
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.outindex bdae8fe00c..21b28fde6e 100644--- a/src/test/regress/expected/cluster.out+++ b/src/test/regress/expected/cluster.out@@ -439,13 +439,28 @@ select * from clstr_temp;
drop table clstr_temp;
RESET SESSION AUTHORIZATION;
--- Check that partitioned tables cannot be clustered+-- Check that partitioned tables can be clustered
CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
+CREATE TABLE clstrpart1 PARTITION OF clstrpart FOR VALUES FROM (1)TO(10) PARTITION BY RANGE (a);+CREATE TABLE clstrpart11 PARTITION OF clstrpart1 FOR VALUES FROM (1)TO(10);+CREATE TABLE clstrpart12 PARTITION OF clstrpart1 FOR VALUES FROM (10)TO(20) PARTITION BY RANGE(a);+CREATE TABLE clstrpart2 PARTITION OF clstrpart FOR VALUES FROM (20)TO(30);
CREATE INDEX clstrpart_idx ON clstrpart (a);
-ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;-ERROR: cannot mark index clustered in partitioned table+ALTER TABLE clstrpart SET WITHOUT CLUSTER;
CLUSTER clstrpart USING clstrpart_idx;
-ERROR: cannot cluster a partitioned table+CLUSTER clstrpart1 USING clstrpart1_a_idx; -- partition which is itself partitioned+CLUSTER clstrpart12 USING clstrpart12_a_idx; -- partition which is itself partitioned, no childs+CLUSTER clstrpart2 USING clstrpart2_a_idx; -- leaf+\d clstrpart+ Partitioned table "public.clstrpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | | +Partition key: RANGE (a)+Indexes:+ "clstrpart_idx" btree (a) CLUSTER+Number of partitions: 2 (Use \d+ to list them.)+
DROP TABLE clstrpart;
-- Test CLUSTER with external tuplesorting
create table clstr_4 as select * from tenk1;
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sqlindex 188183647c..4a76848213 100644--- a/src/test/regress/sql/cluster.sql+++ b/src/test/regress/sql/cluster.sql@@ -196,11 +196,19 @@ drop table clstr_temp;
RESET SESSION AUTHORIZATION;
--- Check that partitioned tables cannot be clustered+-- Check that partitioned tables can be clustered
CREATE TABLE clstrpart (a int) PARTITION BY RANGE (a);
+CREATE TABLE clstrpart1 PARTITION OF clstrpart FOR VALUES FROM (1)TO(10) PARTITION BY RANGE (a);+CREATE TABLE clstrpart11 PARTITION OF clstrpart1 FOR VALUES FROM (1)TO(10);+CREATE TABLE clstrpart12 PARTITION OF clstrpart1 FOR VALUES FROM (10)TO(20) PARTITION BY RANGE(a);+CREATE TABLE clstrpart2 PARTITION OF clstrpart FOR VALUES FROM (20)TO(30);
CREATE INDEX clstrpart_idx ON clstrpart (a);
-ALTER TABLE clstrpart CLUSTER ON clstrpart_idx;+ALTER TABLE clstrpart SET WITHOUT CLUSTER;
CLUSTER clstrpart USING clstrpart_idx;
+CLUSTER clstrpart1 USING clstrpart1_a_idx; -- partition which is itself partitioned+CLUSTER clstrpart12 USING clstrpart12_a_idx; -- partition which is itself partitioned, no childs+CLUSTER clstrpart2 USING clstrpart2_a_idx; -- leaf+\d clstrpart
DROP TABLE clstrpart;
-- Test CLUSTER with external tuplesorting
--
2.17.0
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: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, michael@paquier.xyz, adger.lj@alibaba-inc.com, pgsql-hackers@lists.postgresql.org, wenjing.zwj@alibaba-inc.com, alvherre@2ndquadrant.com
Subject: Re: 回复：how to create index concurrently on partitioned table
In-Reply-To: <20200908023915.GD7158@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox