Thread: BUG #15728: an index that created with keyword ONLY, when add new partition ,should not be auto created

The following bug has been logged on the website:

Bug reference:      15728
Logged by:          DamionZ Zhao
Email address:      zhq651@126.com
PostgreSQL version: 11.2
Operating system:   linux
Description:

1. This is my partitioned table and partition.
postgres=# CREATE TABLE cities (  
postgres(#     city_id      bigserial not null,  
postgres(#     name         text not null,  
postgres(#     population   bigint  
postgres(# ) PARTITION BY LIST (left(lower(name), 1));  
CHECK (city_id != 0)  
) FOR VALUES IN ('a', 'b'); CREATE TABLE
postgres=#   
postgres=# CREATE TABLE cities_ab  
postgres-#     PARTITION OF cities (  
postgres(#     CONSTRAINT city_id_nonzero CHECK (city_id != 0)  
postgres(# ) FOR VALUES IN ('a', 'b'); 

2.create the index with ONLY
postgres=# create index idx_cities_2 on only cities (population);  
CREATE INDEX

3.check: 
3.1 we see its status is INVALID in partitioned table --- ---it's ok
postgres=# \d+ cities
                                                    Table "public.cities"
   Column   |  Type  | Collation | Nullable |                 Default
         | Storage  | Stats target | Description 

------------+--------+-----------+----------+-----------------------------------------+----------+--------------+-------------
 city_id    | bigint |           | not null |
nextval('cities_city_id_seq'::regclass) | plain    |              | 
 name       | text   |           | not null |
         | extended |              | 
 population | bigint |           |          |
         | plain    |              | 
Partition key: LIST ("left"(lower(name), 1))
Indexes:
    "idx_cities_1" btree (name)
    "idx_cities_2" btree (population) INVALID
Partitions: cities_ab FOR VALUES IN ('a', 'b')

3.2 we see that index idx_cities_2 is not created automaticly here.---it's
ok
postgres=# \d+ cities_ab
                                                   Table
"public.cities_ab"
   Column   |  Type  | Collation | Nullable |                 Default
         | Storage  | Stats target | Description 

------------+--------+-----------+----------+-----------------------------------------+----------+--------------+-------------
 city_id    | bigint |           | not null |
nextval('cities_city_id_seq'::regclass) | plain    |              | 
 name       | text   |           | not null |
         | extended |              | 
 population | bigint |           |          |
         | plain    |              | 
Partition of: cities FOR VALUES IN ('a', 'b')
Partition constraint: (("left"(lower(name), 1) IS NOT NULL) AND
("left"(lower(name), 1) = ANY (ARRAY['a'::text, 'b'::text])))
Indexes:
    "cities_ab_name_idx" btree (name)
Check constraints:
    "city_id_nonzero" CHECK (city_id <> 0)

4. when add a new partition.
postgres=# CREATE TABLE cities_cd
postgres-#     PARTITION OF cities (  
postgres(#     CONSTRAINT city_id_nonzero CHECK (city_id != 0)  
postgres(# ) FOR VALUES IN ('c', 'd');  
CREATE TABLE

postgres=# \d+ cities_cd
                                                   Table
"public.cities_cd"
   Column   |  Type  | Collation | Nullable |                 Default
         | Storage  | Stats target | Description 

------------+--------+-----------+----------+-----------------------------------------+----------+--------------+-------------
 city_id    | bigint |           | not null |
nextval('cities_city_id_seq'::regclass) | plain    |              | 
 name       | text   |           | not null |
         | extended |              | 
 population | bigint |           |          |
         | plain    |              | 
Partition of: cities FOR VALUES IN ('c', 'd')
Partition constraint: (("left"(lower(name), 1) IS NOT NULL) AND
("left"(lower(name), 1) = ANY (ARRAY['c'::text, 'd'::text])))
Indexes:
    "cities_cd_name_idx" btree (name)
    "cities_cd_population_idx" btree (population)
Check constraints:
    "city_id_nonzero" CHECK (city_id <> 0)

we see that index on population is created automaticly. Does it make sense?
ONLY's means is different between  created partiton and new partition.