>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
