回复：回复：回复：how to create index concurrently on partitioned table - Mailing list pgsql-hackers
| From | 李杰(慎追) |
|---|---|
| Subject | 回复：回复：回复：how to create index concurrently on partitioned table |
| Date | |
| Msg-id | 8b48c634-d0fb-4f4f-8545-b4ed08a5959c.adger.lj@alibaba-inc.com Whole thread Raw |
| In response to | Re: 回复：回复：how to createindex concurrently on partitioned table (Michael Paquier <michael@paquier.xyz>) |
| List | pgsql-hackers |
> I am afraid that this makes the error handling more complicated, with
> risks of having inconsistent partition trees. That's the point you
> raised. This one is going to need more thoughts.
> risks of having inconsistent partition trees. That's the point you
> raised. This one is going to need more thoughts.
> CIC is an operation that exists while allowing read and writes to
> still happen in parallel, so that's fine IMO if it takes time. Now it
> is true that it may not scale well so we should be careful with the
> approach taken. Also, I think that the case of REINDEX would require
> less work overall because we already have some code in place to gather
> multiple indexes from one or more relations and work on these
> consistently, all at once.
> still happen in parallel, so that's fine IMO if it takes time. Now it
> is true that it may not scale well so we should be careful with the
> approach taken. Also, I think that the case of REINDEX would require
> less work overall because we already have some code in place to gather
> multiple indexes from one or more relations and work on these
> consistently, all at once.
I'm with you on that.
(Scheme 1)
We can refer to the implementation in the ReindexRelationConcurrently,
in the three phases of the REINDEX CONCURRENTLY,
all indexes of the partitions are operated one by one in each phase.
In this way, we can maintain the consistency of the entire partitioned table index.
After we implement CIC in this way, we can also complete reindex partitioned table index concurrently (this is not done now.)
Looking back, let's talk about our original schema.
(Scheme 2)
If CIC is performed one by one on each partition,
how can we make it on subsequent partitions continue when an error occurs in the second partition?
If this problem can be solved, It's not that I can't accept it.
Because a partition CIC error is acceptable, you can reindex it later.
Pseudo indexes on partitioned tables are useless for real queries,
but the indexes on partitions are really useful.
Scheme 1, more elegant, can also implement reindex concurrently on partitioned table,
but the implementation is more complex.
Scheme 2: simple implementation, but not so friendly.
Hi Jsutin,
Which scheme do you think is more helpful to realize CIC?
------------------------------------------------------------------发件人：Michael Paquier <michael@paquier.xyz>发送时间：2020年6月16日(星期二) 09:02收件人：李杰(慎追) <adger.lj@alibaba-inc.com>抄 送：Justin Pryzby <pryzby@telsasoft.com>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>; 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>; Alvaro Herrera <alvherre@2ndquadrant.com>主 题：Re: 回复：回复：how to create index concurrently on partitioned tableOn Mon, Jun 15, 2020 at 09:33:05PM +0800, 李杰(慎追) wrote:
> This is a good idea.
> We should maintain the consistency of the entire partition table.
> However, this is not a small change in the code.
> May be we need to make a new design for DefineIndex function....
Indeed. I have looked at the patch set a bit and here is the related
bit for CIC in 0001, meaning that you handle the basic index creation
for a partition tree within one transaction for each:
+ /*
+ * 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)
+ {
+ PopActiveSnapshot();
+ CommitTransactionCommand();
+ StartTransactionCommand();
+ index_set_state_flags(indexRelationId, INDEX_CREATE_SET_READY);
+
+ CommandCounterIncrement();
+ index_set_state_flags(indexRelationId, INDEX_CREATE_SET_VALID);
}
I am afraid that this makes the error handling more complicated, with
risks of having inconsistent partition trees. That's the point you
raised. This one is going to need more thoughts.
> But most importantly, if we use three steps to implement CIC,
> we will spend more time than ordinary tables, especially in high
> concurrency cases. To wait for one of partitions which the users to
> use frequently, the creation of other partition indexes is delayed.
> Is it worth doing this?
CIC is an operation that exists while allowing read and writes to
still happen in parallel, so that's fine IMO if it takes time. Now it
is true that it may not scale well so we should be careful with the
approach taken. Also, I think that the case of REINDEX would require
less work overall because we already have some code in place to gather
multiple indexes from one or more relations and work on these
consistently, all at once.
--
Michael
pgsql-hackers by date: