= 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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Marcelo Marques
Дата:
Сообщение: Re: BUG #17588: RHEL 8 nothing provides libarmadillo.so.10 (64bit) needed by gdal-libs3x*
Следующее
От: Daniel Gustafsson
Дата:
Сообщение: Re: BUG #17349: Function gen_random_uuid duplicates in pgcrypto and core