From: | 李杰(慎追) <adger(dot)lj(at)alibaba-inc(dot)com> |
---|---|
To: | "Justin Pryzby" <pryzby(at)telsasoft(dot)com> |
Cc: | "pgsql-hackers" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)com> |
Subject: | 回复:how to create index concurrently on paritioned table |
Date: | 2020-06-12 08:06:28 |
Message-ID: | 3c2edeb5-e216-4411-9a74-f27fcc0f6793.adger.lj@alibaba-inc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>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(at)entry=16400,
stmt=stmt(at)entry=0x1aa5e28, indexRelationId=16408, indexRelationId(at)entry=0,
parentIndexId=parentIndexId(at)entry=16406,
parentConstraintId=0, is_alter_table=is_alter_table(at)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(at)entry=16384,
stmt=stmt(at)entry=0x1b35278, indexRelationId=16406, indexRelationId(at)entry=0,
parentIndexId=parentIndexId(at)entry=0,
parentConstraintId=parentConstraintId(at)entry=0, is_alter_table=
is_alter_table(at)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(at)entry=0x1b350c8,
pstmt=pstmt(at)entry=0x1a2bf40,
queryString=queryString(at)entry=0x1a2b2c8 "create index CONCURRENTLY
idxpart_a_idx on idxpart (a);", context=context(at)entry=PROCESS_UTILITY_TOPLEVEL,
params=params(at)entry=0x0,
queryEnv=queryEnv(at)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(at)entry=0x1a8d1f8,
isTopLevel=isTopLevel(at)entry=true, setHoldSnapshot=setHoldSnapshot(at)entry=false,
dest=dest(at)entry=0x1a2c200,
altdest=altdest(at)entry=0x1a2c200, qc=qc(at)entry=0x7ffc86cc7630) at pquery.c:1310
#7 0x0000000000769ed3 in PortalRun (portal=portal(at)entry=0x1a8d1f8, count=count
@entry=9223372036854775807, isTopLevel=isTopLevel(at)entry=true, run_once=run_once
@entry=true, dest=dest(at)entry=0x1a2c200,
altdest=altdest(at)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(at)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(at)entry=3, argv=argv(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | 李杰 (慎追) | 2020-06-12 08:17:34 | 回复:how to create index concurrently on paritioned table |
Previous Message | Michael Paquier | 2020-06-12 07:48:32 | Re: doc review for v13 |