From: | Christophe Pettus <xof(at)thebuild(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | = TRUE vs IS TRUE confuses partition index creation |
Date: | 2022-08-16 21:33:51 |
Message-ID: | 8864BFAA-81FD-4BF9-8E06-7DEB8D4164ED@thebuild.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
This has been tested on 14.5 and 13.7.
When an index is created on the root of a (declarative) partitioned table, that index is also created on the children, unless there is an existing index on that child that matches the definition of the new index. It seems that using `= TRUE` confuses it, compared to `IS TRUE`.
Test case:
BEGIN;
CREATE TABLE public.t (
id bigint NOT NULL,
t timestamp without time zone NOT NULL,
b boolean NOT NULL
) PARTITION BY RANGE (t);
CREATE TABLE public.t_older (
id bigint NOT NULL,
t timestamp without time zone NOT NULL,
b boolean NOT NULL
);
CREATE INDEX ON public.t_older USING btree (id) WHERE b IS TRUE;
CREATE INDEX ON public.t_older USING btree (id) WHERE b = TRUE;
ALTER TABLE t ATTACH PARTITION t_older
FOR VALUES FROM ('2010-01-01') TO ('2022-01-01');
CREATE INDEX ON public.t USING btree (id) WHERE b IS TRUE;
CREATE INDEX ON public.t USING btree (id) WHERE b = TRUE;
COMMIT;
The result is:
xof=# \d t
Partitioned table "public.t"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
t | timestamp without time zone | | not null |
b | boolean | | not null |
Partition key: RANGE (t)
Indexes:
"t_id_idx" btree (id) WHERE b IS TRUE
"t_id_idx1" btree (id) WHERE b = true
Number of partitions: 1 (Use \d+ to list them.)
fin_test=# \d t_older
Table "public.t_older"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
t | timestamp without time zone | | not null |
b | boolean | | not null |
Partition of: t FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2022-01-01 00:00:00')
Indexes:
"t_older_id_idx" btree (id) WHERE b IS TRUE -- Correctly does not create a new index
"t_older_id_idx1" btree (id) WHERE b = true
"t_older_id_idx2" btree (id) WHERE b = true -- Unexpected duplicated index
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2022-08-16 21:40:15 | Re: BUG #17349: Function gen_random_uuid duplicates in pgcrypto and core |
Previous Message | Marcelo Marques | 2022-08-16 20:39:59 | Re: BUG #17588: RHEL 8 nothing provides libarmadillo.so.10 (64bit) needed by gdal-libs3x* |