= 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

  1. Fix subtly-incorrect matching of parent and child partitioned indexes.