Home > mailing lists

回复：how to create index concurrently on partitioned table - Mailing list pgsql-hackers

From	李杰(慎追)
Subject	回复：how to create index concurrently on partitioned table
Date	June 15, 2020 15:15:05
Msg-id	55841c84-4ab6-4c79-9d1c-f67db2b229db.adger.lj@alibaba-inc.com Whole thread Raw
In response to	Re: how to create index concurrently on partitioned table (Justin Pryzby <pryzby@telsasoft.com>)
Responses	Re: 回复：how to create indexconcurrently on partitioned table
List	pgsql-hackers

Tree view

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

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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"

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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"

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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"

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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"

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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"

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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"

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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"

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

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@telsasoft.com>
发送时间：2020年6月13日(星期六) 02:15
收件人：Michael Paquier <michael@paquier.xyz>; 李杰(慎追) <adger.lj@alibaba-inc.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 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

pgsql-hackers by date:

From: "Inoue, Hiroshi"
Date: 15 June 2020, 14:50:23
Subject: Re: Removal of currtid()/currtid2() and some table AM cleanup

From: Michael Paquier
Date: 15 June 2020, 15:20:39
Subject: Re: doc review for v13

回复：how to create index concurrently on partitioned table - Mailing list pgsql-hackers

Previous

Next