回复：how to create index concurrently on paritioned table - Mailing list pgsql-hackers
| From | 李杰(慎追) |
|---|---|
| Subject | 回复：how to create index concurrently on paritioned table |
| Date | |
| Msg-id | 3c2edeb5-e216-4411-9a74-f27fcc0f6793.adger.lj@alibaba-inc.com Whole thread Raw |
| In response to | Re: how to create index concurrently on paritioned table (Justin Pryzby <pryzby@telsasoft.com>) |
| Responses |
Re: how to create index concurrently on partitioned table
|
| List | pgsql-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?
> >
> > 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.
#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);
Thank you very much,
Regards, Adger
pgsql-hackers by date: