BUG #17412: CREATE INDEX on a partitioned table doesn't recognize existing partial indexes on partitions - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17412
Logged by:          Alexander Kukushkin
Email address:      cyberdemn@gmail.com
PostgreSQL version: 13.5
Operating system:   Ubuntu 18.04
Description:

The CREATE INDEX CONCURRENTLY isn't supported on a partitioned table. As a
workaround, people usually create indexes concurrently on partitions and
after that, the CREATE INDEX on a partitioned table. Normally the last one
is able to recognize already existing indexes and only updates pg_catalog.

This technique mostly works perfectly, but recently we hit the case when the
CREATE INDEX on a partitioned table wasn't able to identify already existing
partial indexes on partitions.

Below you can find a simple and reproducible test case:

localhost/test=# create table test(type text not null, last_modified
timestamp with time zone not null) partition by range (last_modified);
CREATE TABLE
localhost/test=# create table test_1 partition of test for values from
('2022-01-01') to ('2022-02-01');
CREATE TABLE
localhost/test=# create table test_2 partition of test for values from
('2022-02-01') to ('2022-03-01');
CREATE TABLE
localhost/test=# create index CONCURRENTLY ON test_1 (last_modified) where
type = ANY (ARRAY['foo'::text, 'bar'::text]);
CREATE INDEX
localhost/test=# create index CONCURRENTLY ON test_2 (last_modified) where
type = ANY (ARRAY['foo'::text, 'bar'::text]);
CREATE INDEX
localhost/test=# create index ON test (last_modified) where type = ANY
(ARRAY['foo'::text, 'bar'::text]);
CREATE INDEX
localhost/test=# \d test
                      Partitioned table "public.test"
    Column     │           Type           │ Collation │ Nullable │ Default

───────────────┼──────────────────────────┼───────────┼──────────┼─────────
 type          │ text                     │           │ not null │ 
 last_modified │ timestamp with time zone │           │ not null │ 
Partition key: RANGE (last_modified)
Indexes:
    "test_last_modified_idx" btree (last_modified) WHERE type = ANY
(ARRAY['foo'::text, 'bar'::text])
Number of partitions: 2 (Use \d+ to list them.)

localhost/test=# \d test_1
                           Table "public.test_1"
    Column     │           Type           │ Collation │ Nullable │ Default

───────────────┼──────────────────────────┼───────────┼──────────┼─────────
 type          │ text                     │           │ not null │ 
 last_modified │ timestamp with time zone │           │ not null │ 
Partition of: test FOR VALUES FROM ('2022-01-01 00:00:00+01') TO
('2022-02-01 00:00:00+01')
Indexes:
    "test_1_last_modified_idx" btree (last_modified) WHERE type = ANY
(ARRAY['foo'::text, 'bar'::text])
    "test_1_last_modified_idx1" btree (last_modified) WHERE type = ANY
(ARRAY['foo'::text, 'bar'::text])

localhost/test=# \d test_2
                           Table "public.test_2"
    Column     │           Type           │ Collation │ Nullable │ Default

───────────────┼──────────────────────────┼───────────┼──────────┼─────────
 type          │ text                     │           │ not null │ 
 last_modified │ timestamp with time zone │           │ not null │ 
Partition of: test FOR VALUES FROM ('2022-02-01 00:00:00+01') TO
('2022-03-01 00:00:00+01')
Indexes:
    "test_2_last_modified_idx" btree (last_modified) WHERE type = ANY
(ARRAY['foo'::text, 'bar'::text])
    "test_2_last_modified_idx1" btree (last_modified) WHERE type = ANY
(ARRAY['foo'::text, 'bar'::text])


As one can see, the "create index ON test (last_modified) where type = ANY
(ARRAY['foo'::text, 'bar'::text])" has created duplicated indexes
test_1_last_modified_idx1 and test_2_last_modified_idx1 despite the fact
that there are already existing indexes test_1_last_modified_idx and
test_1_last_modified_idx1.

We hit this problem with 13.5, but for example 14.2 is also affected.

P.S. the feature certainly works for partial indexes that don't use ANY or
IN operators