BUG #15728: an index that created with keyword ONLY, when add new partition ,should not be auto created - Mailing list pgsql-bugs
From | PG Bug reporting form |
---|---|
Subject | BUG #15728: an index that created with keyword ONLY, when add new partition ,should not be auto created |
Date | |
Msg-id | 15728-7613598732685b7a@postgresql.org Whole thread Raw |
Responses |
Re: BUG #15728: an index that created with keyword ONLY, when add newpartition ,should not be auto created
|
List | pgsql-bugs |
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.
pgsql-bugs by date: