Re: Missing constraint when duplicated unique index ?

From: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Missing constraint when duplicated unique index ?
Date: 2025-03-13 12:17:35
Message-ID: 202503131217.thfzldlgjwm7@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2025-03-13 12:19:31 Re: Random pg_upgrade 004_subscription test failure on drongo
Previous Message Ryo Kanbayashi 2025-03-13 12:08:28 Re: PGSERVICEFILE as part of a normal connection string