回复：how to create index concurrently on partitioned table

> My (tentative) understanding is that these types of things should use a
> "subtransaction" internally.. So if the toplevel transaction rolls back, its
> changes are lost. In some cases, it might be desirable to not roll back, in
> which case the user(client) should first create indexes (concurrently if
> needed) on every child, and then later create index on parent (that has the
> advtantage of working on older servers, too).

Hi Justin,
I have a case here, you see if it meets your expectations.

`````
postgres=# CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE
postgres=# CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (25);
CREATE TABLE
postgres=# CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (25) TO (50);
CREATE TABLE
postgres=# CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (50) TO (75);
CREATE TABLE
postgres=# INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 74) i;
INSERT 0 75
postgres=# insert into prt1 values (26,1,'FM0026');
INSERT 0 1
postgres=# create unique index CONCURRENTLY idexpart_cic on prt1 (a);
ERROR: could not create unique index "prt1_p2_a_idx"
DETAIL: Key (a)=(26) is duplicated.
postgres=# \d+ prt1
Partitioned table "public.prt1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition key: RANGE (a)
Indexes:
"idexpart_cic" UNIQUE, btree (a) INVALID
Partitions: prt1_p1 FOR VALUES FROM (0) TO (25),
prt1_p2 FOR VALUES FROM (25) TO (50),
prt1_p3 FOR VALUES FROM (50) TO (75)

postgres=# \d+ prt1_p1
Table "public.prt1_p1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition of: prt1 FOR VALUES FROM (0) TO (25)
Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 25))
Indexes:
"prt1_p1_a_idx" UNIQUE, btree (a)
Access method: heap

postgres=# \d+ prt1_p2
Table "public.prt1_p2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition of: prt1 FOR VALUES FROM (25) TO (50)
Partition constraint: ((a IS NOT NULL) AND (a >= 25) AND (a < 50))
Indexes:
"prt1_p2_a_idx" UNIQUE, btree (a) INVALID
Access method: heap

postgres=# \d+ prt1_p3
Table "public.prt1_p3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition of: prt1 FOR VALUES FROM (50) TO (75)
Partition constraint: ((a IS NOT NULL) AND (a >= 50) AND (a < 75))
Access method: heap
```````
As shown above, an error occurred while creating an index in the second partition.
It can be clearly seen that the index of the partitioned table is invalid
and the index of the first partition is normal, the second partition is invalid,
and the Third Partition index does not exist at all.

But we do the following tests again:
```
postgres=# truncate table prt1;
TRUNCATE TABLE
postgres=# INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 74) i;
INSERT 0 75
postgres=# insert into prt1 values (51,1,'FM0051');
INSERT 0 1
postgres=# drop index idexpart_cic;
DROP INDEX
postgres=# create unique index CONCURRENTLY idexpart_cic on prt1 (a);
ERROR: could not create unique index "prt1_p3_a_idx"
DETAIL: Key (a)=(51) is duplicated.
postgres=# \d+ prt1
Partitioned table "public.prt1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition key: RANGE (a)
Indexes:
"idexpart_cic" UNIQUE, btree (a) INVALID
Partitions: prt1_p1 FOR VALUES FROM (0) TO (25),
prt1_p2 FOR VALUES FROM (25) TO (50),
prt1_p3 FOR VALUES FROM (50) TO (75)

postgres=# \d+ prt1_p1
Table "public.prt1_p1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition of: prt1 FOR VALUES FROM (0) TO (25)
Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 25))
Indexes:
"prt1_p1_a_idx" UNIQUE, btree (a)
Access method: heap

postgres=# \d+ prt1_p2
Table "public.prt1_p2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition of: prt1 FOR VALUES FROM (25) TO (50)
Partition constraint: ((a IS NOT NULL) AND (a >= 25) AND (a < 50))
Indexes:
"prt1_p2_a_idx" UNIQUE, btree (a)
Access method: heap

postgres=# \d+ prt1_p3
Table "public.prt1_p3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition of: prt1 FOR VALUES FROM (50) TO (75)
Partition constraint: ((a IS NOT NULL) AND (a >= 50) AND (a < 75))
Access method: heap
```

Now we can see that the first two partitions have indexes,
but the third partition has no indexes due to an error.
Therefore, in our first case, it should not be what we expected that the third partition has no index.
That is to say, when our CIC goes wrong, either roll back all or go down instead of stopping in the middle.
This is my shallow opinion, please take it as your reference.

Thank you very much,
Regards, Adger

------------------------------------------------------------------
发件人：Justin Pryzby <pryzby(at)telsasoft(dot)com>
发送时间：2020年6月13日(星期六) 02:15
收件人：Michael Paquier <michael(at)paquier(dot)xyz>; 李杰(慎追) <adger(dot)lj(at)alibaba-inc(dot)com>
抄　送：pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>; 曾文旌(义从) <wenjing(dot)zwj(at)alibaba-inc(dot)com>; Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
主　题：Re: how to create index concurrently on partitioned table

On Fri, Jun 12, 2020 at 04:17:34PM +0800, 李杰(慎追) wrote:
> 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?

My (tentative) understanding is that these types of things should use a
"subtransaction" internally.. So if the toplevel transaction rolls back, its
changes are lost. In some cases, it might be desirable to not roll back, in
which case the user(client) should first create indexes (concurrently if
needed) on every child, and then later create index on parent (that has the
advtantage of working on older servers, too).

postgres=# SET client_min_messages=debug;
postgres=# CREATE INDEX ON t(i);
DEBUG: building index "t1_i_idx" on table "t1" with request for 1 parallel worker
DEBUG: index "t1_i_idx" can safely use deduplication
DEBUG: creating and filling new WAL file
DEBUG: done creating and filling new WAL file
DEBUG: creating and filling new WAL file
DEBUG: done creating and filling new WAL file
DEBUG: building index "t2_i_idx" on table "t2" with request for 1 parallel worker
^C2020-06-12 13:08:17.001 CDT [19291] ERROR: canceling statement due to user request
2020-06-12 13:08:17.001 CDT [19291] STATEMENT: CREATE INDEX ON t(i);
2020-06-12 13:08:17.001 CDT [27410] FATAL: terminating connection due to administrator command
2020-06-12 13:08:17.001 CDT [27410] STATEMENT: CREATE INDEX ON t(i);
Cancel request sent

If the index creation is interrupted at this point, no indexes will exist.

On Fri, Jun 12, 2020 at 04:06:28PM +0800, 李杰(慎追) wrote:
> >On Sat, Jun 06, 2020 at 09:23:32AM -0500, Justin Pryzby wrote:
> > 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.
>
> 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:

> 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.

Thanks, fixed.

On Fri, Jun 12, 2020 at 04:20:17PM +0900, Michael Paquier wrote:
> When it comes to test behaviors specific to partitioning, there are in
> my experience three things to be careful about and stress in the tests:
> - Use at least two layers of partitioning.
> - Include into the partition tree a partition that has no leaf
> partitions.
> - Test the commands on the top-most parent, a member in the middle of
> the partition tree, the partition with no leaves, and one leaf, making
> sure that relfilenode changes where it should and that partition trees
> remain intact (you can use pg_partition_tree() for that.)

Added, thanks for looking.

--
Justin