= TRUE vs IS TRUE confuses partition index creation

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

Responses

Browse pgsql-bugs by date

  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*