= TRUE vs IS TRUE confuses partition index creation
Christophe Pettus <xof@thebuild.com>
From: Christophe Pettus <xof@thebuild.com>
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2022-08-16T21:33:51Z
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
Commits
-
Fix subtly-incorrect matching of parent and child partitioned indexes.
- e6dbb48487fa 16.0 landed
- 2a42c1c51d15 15.0 landed
- 9f0073ef7dac 13.9 landed
- 3bfea5cbba98 14.6 landed
- 2cf16cd7497d 12.13 landed
- 116f20f92656 11.18 landed