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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      15728
Logged by:          DamionZ Zhao
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Tue, Apr 2, 2019 at 4:04 AM PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15728
Logged by:          DamionZ Zhao
Email address:      [hidden email]
PostgreSQL version: 11.2
Operating system:   linux
Description:       
[...]
we see that index on population is created automaticly. Does it make sense?
ONLY's means is different between  created partiton and new partition.

Documented behavior.

"""
If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.
"""


David J.