Re: Missing constraint when duplicated unique index ?

On 2025-Mar-12, Marcos Pegoraro wrote:

> CREATE TABLE table_test (
> foo text NOT NULL,
> CONSTRAINT test_pk PRIMARY KEY (foo),
> CONSTRAINT test_uq UNIQUE (foo)
> );
> building index "pg_toast_29364884_index" on table "pg_toast_29364884"
> serially
> building index "test_pk" on table "table_test" serially
>
> pg_class has 3 records, table, its pk and unique indexes.
> select * from pg_class where relname ~ '
> pg_toast_29364884_index|test_pk|table_test';
>
> but pg_constraint has just one record.
> select * from pg_constraint where conrelid::regclass::text ~
> 'table_test|pg_toast_29364884'
>
> Is that correct ? That second index exists but not its constraint.
> This happens only when the primary key field is text, so it needs toast.

I tried this example all the way back to pg 9.5, and they all end up
with a single constraint and a single index -- namely, the test_pk
constraint and corresponding index. There's no second index and no
second constraint. test_uq goes ignored. Also, the fact that the
column is text plays no role: you get exactly the same with an integer
column.

Note that for these purposes, the toast table is an unrelated entity to
the main table. Any indexes that it has are independent, and no
constraints are ever defined (or needed). Do not confuse yourself by
querying for them!

This happens because of very old code in transformIndexConstraints().

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)