From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | zhq651(at)126(dot)com |
Subject: | BUG #15728: an index that created with keyword ONLY, when add new partition ,should not be auto created |
Date: | 2019-04-02 10:36:21 |
Message-ID: | 15728-7613598732685b7a@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15728
Logged by: DamionZ Zhao
Email address: zhq651(at)126(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-04-02 15:12:35 | Re: BUG #15728: an index that created with keyword ONLY, when add new partition ,should not be auto created |
Previous Message | PG Bug reporting form | 2019-04-02 10:30:15 | BUG #15727: PANIC: cannot abort transaction 295144144, it was already committed |