pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed how to create index concurrently on paritioned table
5+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2020-09-08 a6642b3ae0 Add support for partitioned tables and indexes in REINDEX
* how to create index concurrently on paritioned table
@ 2020-06-03 12:22 李杰(慎追) <adger.lj@alibaba-inc.com>
0 siblings, 1 reply; 5+ messages in thread
From: 李杰(慎追) @ 2020-06-03 12:22 UTC (permalink / raw)
To: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; +Cc: 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
Hi hackers,
Partitioning is necessary for very large tables.
However, I found that postgresql does not support create index concurrently on partitioned tables.
The document show that we need to create an index on each partition individually and then finally create the partitioned index non-concurrently.
This is undoubtedly a complex operation for DBA, especially when there are many partitions.
Therefore, I wonder why pg does not support concurrent index creation on partitioned tables?
What are the difficulties of this function?
If I want to implement it, what should I pay attention?
Sincerely look forward to your reply.
Regards & Thanks Adger
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to create index concurrently on paritioned table
@ 2020-06-06 14:23 Justin Pryzby <pryzby@telsasoft.com>
parent: 李杰(慎追) <adger.lj@alibaba-inc.com>
0 siblings, 2 replies; 5+ messages in thread
From: Justin Pryzby @ 2020-06-06 14:23 UTC (permalink / raw)
To: 李杰(慎追) <adger.lj@alibaba-inc.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
On Wed, Jun 03, 2020 at 08:22:29PM +0800, 李杰(慎追) wrote:
> Partitioning is necessary for very large tables.
> However, I found that postgresql does not support create index concurrently on partitioned tables.
> The document show that we need to create an index on each partition individually and then finally create the partitioned index non-concurrently.
> This is undoubtedly a complex operation for DBA, especially when there are many partitions.
> Therefore, I wonder why pg does not support concurrent index creation on partitioned tables?
> What are the difficulties of this function?
> If I want to implement it, what should I pay attention?
Maybe I'm wrong, but I don't think there's any known difficulty - just that
nobody did it yet. You should pay attention to what happens on error, but
hopefully you wouldn't need to add much code and can rely on existing code to
paths to handle that right.
I think you'd look at the commits and code implementing indexes on partitioned
tables and CREATE INDEX CONCURRENTLY. And maybe any following commits with
fixes.
You'd first loop around all children (recursively if there are partitions which
are themselves partitioned) and create indexes concurrently.
--
Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to create index concurrently on paritioned table
@ 2020-06-07 18:04 Justin Pryzby <pryzby@telsasoft.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 5+ messages in thread
From: Justin Pryzby @ 2020-06-07 18:04 UTC (permalink / raw)
To: 李杰(慎追) <adger.lj@alibaba-inc.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
On Sat, Jun 06, 2020 at 09:23:32AM -0500, Justin Pryzby wrote:
> On Wed, Jun 03, 2020 at 08:22:29PM +0800, 李杰(慎追) wrote:
> > Partitioning is necessary for very large tables.
> > However, I found that postgresql does not support create index concurrently on partitioned tables.
> > The document show that we need to create an index on each partition individually and then finally create the partitioned index non-concurrently.
> > This is undoubtedly a complex operation for DBA, especially when there are many partitions.
>
> > Therefore, I wonder why pg does not support concurrent index creation on partitioned tables?
> > What are the difficulties of this function?
> > If I want to implement it, what should I pay attention?
>
> Maybe I'm wrong, but I don't think there's any known difficulty - just that
> nobody did it yet.
I said that but I was actually thinking about the code for "REINDEX
CONCURRENTLY" (which should also handle partitioned tables).
I looked at CIC now and came up with the attached. All that's needed to allow
this case is to close the relation before recursing to partitions - it needs to
be closed before calling CommitTransactionCommand(). There's probably a better
way to write this, but I can't see that there's anything complicated about
handling partitioned tables.
--
Justin
Attachments:
v1-0001-Allow-CREATE-INDEX-CONCURRENTLY-on-partitioned-ta.patch (text/x-diff, 5.1K) download
From de5f151805946b6999aa25aaddd9ebaaf91ccff2 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 6 Jun 2020 17:42:23 -0500
Subject: [PATCH v1] Allow CREATE INDEX CONCURRENTLY on partitioned table
---
doc/src/sgml/ref/create_index.sgml | 9 -----
src/backend/commands/indexcmds.c | 54 +++++++++++++++++-------------
2 files changed, 31 insertions(+), 32 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index ff87b2d28f..e1298b8523 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.c
index 2baca12c5f..d2809c85a0 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -652,17 +652,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),
@@ -1139,15 +1128,23 @@ DefineIndex(Oid relationId,
CreateComments(indexRelationId, RelationRelationId, 0,
stmt->idxcomment);
+ /* save lockrelid and locktag for below */
+ heaprelid = rel->rd_lockInfo.lockRelId;
+ SET_LOCKTAG_RELATION(heaplocktag, heaprelid.dbId, heaprelid.relId);
+
if (partitioned)
{
+ char *relname = pstrdup(RelationGetRelationName(rel));
+
/*
* Unless caller specified to skip this step (via ONLY), process each
* partition to make sure they all contain a corresponding index.
*
* If we're called internally (no stmt->relation), recurse always.
*/
- if (!stmt->relation || stmt->relation->inh)
+ if (stmt->relation && !stmt->relation->inh)
+ table_close(rel, NoLock);
+ else
{
PartitionDesc partdesc = RelationGetPartitionDesc(rel);
int nparts = partdesc->nparts;
@@ -1166,6 +1163,8 @@ DefineIndex(Oid relationId,
for (i = 0; i < numberOfKeyAttributes; i++)
opfamOids[i] = get_opclass_family(classObjectId[i]);
+ table_close(rel, NoLock);
+
/*
* For each partition, scan all existing indexes; if one matches
* our index definition and is not already attached to some other
@@ -1196,9 +1195,9 @@ DefineIndex(Oid relationId,
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot create unique index on partitioned table \"%s\"",
- RelationGetRelationName(rel)),
+ relname),
errdetail("Table \"%s\" contains partitions that are foreign tables.",
- RelationGetRelationName(rel))));
+ relname)));
table_close(childrel, lockmode);
continue;
@@ -1365,21 +1364,35 @@ DefineIndex(Oid relationId,
}
}
+ /*
+ * CIC needs to mark a partitioned table as VALID, which itself
+ * requires setting READY, which is unset for CIC (even though
+ * it's meaningless for an index without storage).
+ */
+ if (concurrent)
+ {
+ if (ActiveSnapshotSet())
+ PopActiveSnapshot();
+ CommitTransactionCommand();
+ StartTransactionCommand();
+ 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;
}
+ table_close(rel, NoLock);
if (!concurrent)
{
- /* Close the heap and we're done, in the non-concurrent case */
- table_close(rel, NoLock);
-
/* If this is the top-level index, we're done. */
if (!OidIsValid(parentIndexId))
pgstat_progress_end_command();
@@ -1387,11 +1400,6 @@ DefineIndex(Oid relationId,
return address;
}
- /* save lockrelid and locktag for below, then close rel */
- 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
* visible to other transactions before we start to build the index. That
--
2.17.0
* 回复：how to create index concurrently on paritioned table
@ 2020-06-12 08:06 李杰(慎追) <adger.lj@alibaba-inc.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 5+ messages in thread
From: 李杰(慎追) @ 2020-06-12 08:06 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
>On Sat, Jun 06, 2020 at 09:23:32AM -0500, Justin Pryzby wrote:
> > On Wed, Jun 03, 2020 at 08:22:29PM +0800, 李杰(慎追) wrote:
> > > Partitioning is necessary for very large tables.
> > > However, I found that postgresql does not support create index concurrently on partitioned tables.
> > > The document show that we need to create an index on each partition individually and then finally create the partitioned index non-concurrently.
> > > This is undoubtedly a complex operation for DBA, especially when there are many partitions.
> >
> > > Therefore, I wonder why pg does not support concurrent index creation on partitioned tables?
> > > What are the difficulties of this function?
> > > If I want to implement it, what should I pay attention?
> >
> > Maybe I'm wrong, but I don't think there's any known difficulty - just that
> > nobody did it yet.
> I said that but I was actually thinking about the code for "REINDEX
> CONCURRENTLY" (which should also handle partitioned tables).
> I looked at CIC now and came up with the attached. All that's needed to allow
> this case is to close the relation before recursing to partitions - it needs to
> be closed before calling CommitTransactionCommand(). There's probably a better
> way to write this, but I can't see that there's anything complicated about
> handling partitioned tables.
Hi, Justin Pryzby
I'm so sorry about getting back late.
Thank you very much for helping me consider this issue.
I compiled the patch v1 you provided. And I patch v2-001 again to enter postgresql.
I got a coredump that was easy to reproduce. As follows:
#0 PopActiveSnapshot () at snapmgr.c:822
#1 0x00000000005ca687 in DefineIndex (relationId=relationId@entry=16400,
stmt=stmt@entry=0x1aa5e28, indexRelationId=16408, indexRelationId@entry=0,
parentIndexId=parentIndexId@entry=16406,
parentConstraintId=0, is_alter_table=is_alter_table@entry=false,
check_rights=true, check_not_in_use=true, skip_build=false, quiet=false)
at indexcmds.c:1426
#2 0x00000000005ca5ab in DefineIndex (relationId=relationId@entry=16384,
stmt=stmt@entry=0x1b35278, indexRelationId=16406, indexRelationId@entry=0,
parentIndexId=parentIndexId@entry=0,
parentConstraintId=parentConstraintId@entry=0, is_alter_table=
is_alter_table@entry=false, check_rights=true, check_not_in_use=true,
skip_build=false, quiet=false) at indexcmds.c:1329
#3 0x000000000076bf80 in ProcessUtilitySlow (pstate=pstate@entry=0x1b350c8,
pstmt=pstmt@entry=0x1a2bf40,
queryString=queryString@entry=0x1a2b2c8 "create index CONCURRENTLY
idxpart_a_idx on idxpart (a);", context=context@entry=PROCESS_UTILITY_TOPLEVEL,
params=params@entry=0x0,
queryEnv=queryEnv@entry=0x0, qc=0x7ffc86cc7630, dest=0x1a2c200) at
utility.c:1474
#4 0x000000000076afeb in standard_ProcessUtility (pstmt=0x1a2bf40,
queryString=0x1a2b2c8 "create index CONCURRENTLY idxpart_a_idx on idxpart (a);",
context=PROCESS_UTILITY_TOPLEVEL, params=0x0,
queryEnv=0x0, dest=0x1a2c200, qc=0x7ffc86cc7630) at utility.c:1069
#5 0x0000000000768992 in PortalRunUtility (portal=0x1a8d1f8, pstmt=0x1a2bf40,
isTopLevel=<optimized out>, setHoldSnapshot=<optimized out>, dest=<optimized out>,
qc=0x7ffc86cc7630) at pquery.c:1157
#6 0x00000000007693f3 in PortalRunMulti (portal=portal@entry=0x1a8d1f8,
isTopLevel=isTopLevel@entry=true, setHoldSnapshot=setHoldSnapshot@entry=false,
dest=dest@entry=0x1a2c200,
altdest=altdest@entry=0x1a2c200, qc=qc@entry=0x7ffc86cc7630) at pquery.c:1310
#7 0x0000000000769ed3 in PortalRun (portal=portal@entry=0x1a8d1f8, count=count
@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once
@entry=true, dest=dest@entry=0x1a2c200,
altdest=altdest@entry=0x1a2c200, qc=0x7ffc86cc7630) at pquery.c:779
#8 0x0000000000765b06 in exec_simple_query (query_string=0x1a2b2c8 "create
index CONCURRENTLY idxpart_a_idx on idxpart (a);") at postgres.c:1239
#9 0x0000000000767de5 in PostgresMain (argc=<optimized out>, argv=argv@entry
=0x1a552c8, dbname=<optimized out>, username=<optimized out>) at postgres.c:4315
#10 0x00000000006f2b23 in BackendRun (port=0x1a4d1e0, port=0x1a4d1e0) at postmaster.c:4523
#11 BackendStartup (port=0x1a4d1e0) at postmaster.c:4215
#12 ServerLoop () at postmaster.c:1727
#13 0x00000000006f3a1f in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x1a25ea0)
at postmaster.c:1400
#14 0x00000000004857f9 in main (argc=3, argv=0x1a25ea0) at main.c:210
You can re-produce it like this:
```
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 table idxpart2 partition of idxpart for values from (10) to (20);
create index CONCURRENTLY idxpart_a_idx on idxpart (a);
````
I have been trying to get familiar with the source code of create index.
Can you solve this bug first? I will try my best to implement CIC with you.
Next, I will read your patchs v2-002 and v2-003.
Thank you very much,
Regards, Adger
^ permalink raw reply [nested|flat] 5+ messages in thread
* 回复：how to create index concurrently on paritioned table
@ 2020-06-12 08:17 李杰(慎追) <adger.lj@alibaba-inc.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 5+ messages in thread
From: 李杰(慎追) @ 2020-06-12 08:17 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>
Hi Justin,
> Maybe I'm wrong, but I don't think there's any known difficulty - just that> nobody did it yet. You should pay attention to what happens on error, but
> hopefully you wouldn't need to add much code and can rely on existing code to
> paths to handle that right.
yes, I am trying to learn the code of index definition.
> I think you'd look at the commits and code implementing indexes on partitioned
> tables and CREATE INDEX CONCURRENTLY. And maybe any following commits with
> fixes.
> You'd first loop around all children (recursively if there are partitions which
> are themselves partitioned) and create indexes concurrently.
As we all know, CIC has three transactions. If we recursively in n partitioned tables,
it will become 3N transactions. If an error occurs in these transactions, we have too many things to deal...
If an error occurs when an index is created in one of the partitions,
what should we do with our new index?
Thank you very much,
Regards, Adger
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-06-12 08:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-03 12:22 how to create index concurrently on paritioned table 李杰(慎追) <adger.lj@alibaba-inc.com>
2020-06-06 14:23 ` Justin Pryzby <pryzby@telsasoft.com>
2020-06-07 18:04 ` Justin Pryzby <pryzby@telsasoft.com>
2020-06-12 08:06 ` 回复：how to create index concurrently on paritioned table 李杰(慎追) <adger.lj@alibaba-inc.com>
2020-06-12 08:17 ` 回复：how to create index concurrently on paritioned table 李杰(慎追) <adger.lj@alibaba-inc.com>
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