= TRUE vs IS TRUE confuses partition index creation
От | Christophe Pettus |
---|---|
Тема | = TRUE vs IS TRUE confuses partition index creation |
Дата | |
Msg-id | 8864BFAA-81FD-4BF9-8E06-7DEB8D4164ED@thebuild.com обсуждение исходный текст |
Ответы |
Re: = TRUE vs IS TRUE confuses partition index creation
|
Список | 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, unlessthere 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
В списке pgsql-bugs по дате отправления: