Thread: Missing constraint when duplicated unique index ?

Missing constraint when duplicated unique index ?

From

Marcos Pegoraro

Date:

12 March, 22:52:16

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.

regards

Marcos

Re: Missing constraint when duplicated unique index ?

From

Álvaro Herrera

Date:

13 March, 15:17:35

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)

Re: Missing constraint when duplicated unique index ?

From

Marcos Pegoraro

Date:

13 March, 16:21:18

Em qui., 13 de mar. de 2025 às 09:17, Álvaro Herrera <alvherre@alvh.no-ip.org> escreveu:

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.

PostgreSQL 17.0 (Ubuntu 17.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

test=# set client_min_messages = debug; -- only to get toast table name

test=# CREATE TABLE table_test (
foo text NOT NULL,
CONSTRAINT test_pk PRIMARY KEY (foo),
CONSTRAINT test_uq UNIQUE (foo)
);
building index "pg_toast_29368336_index" on table "pg_toast_29368336" serially
CREATE TABLE / PRIMARY KEY will create implicit index "test_pk" for table "table_test"
building index "test_pk" on table "table_test" serially

test=# select relname, relkind from pg_class where relname ~ 'pg_toast_29368336|pg_toast_29368336_index|test_pk|table_test';
relname | relkind
-------------------------+---------
pg_toast_29368336 | t
pg_toast_29368336_index | i
table_test | r
test_pk | i
(4 rows)

test=# select conname, contype, conrelid::regclass from pg_constraint where conrelid::regclass::text ~ 'table_test|pg_toast_29368336';
conname | contype | conrelid
---------+---------+------------
test_pk | p | table_test
(1 row)

test=# select indexrelid::regclass, indrelid::regclass, indisunique from pg_index
where indrelid::regclass::text ~ 'table_test|pg_toast_29368336'
indexrelid | indrelid | indisunique
----------------------------------+----------------------------+-------------
pg_toast.pg_toast_29368336_index | pg_toast.pg_toast_29368336 | t
test_pk | table_test | t
(2 rows)

I know Table and Toast are separate relations but there are two indexes, so the question is,
will there be any additional check when insert/update because there exists a second unique index ?

Is it intentional to have this unique index without a related constraint ?

regards

Marcos