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

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.

Responses

Browse pgsql-bugs by date

  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