Thread: BUG #17118: ALTER TABLE doesn't discard redundant UNIQUE constraints as CREATE TABLE will
BUG #17118: ALTER TABLE doesn't discard redundant UNIQUE constraints as CREATE TABLE will
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 17118 Logged by: Richard Zuber Email address: zuberre@gmail.com PostgreSQL version: 13.3 Operating system: Debian 13.3-1.pgdg100+1 (Docker Image) Description: Hello, I have created a table in a fresh database like so: CREATE TABLE foo ( foo_id int NOT NULL, CONSTRAINT foo_pk PRIMARY KEY (foo_id), CONSTRAINT foo_uq UNIQUE (foo_id) ); In order to view the indexes created, I run the following statement: SELECT idx.relname AS index_name, insp.nspname AS index_schema, tbl.relname AS table_name, tnsp.nspname AS table_schema FROM pg_index pgi JOIN pg_class idx ON idx.oid = pgi.indexrelid JOIN pg_namespace insp ON insp.oid = idx.relnamespace JOIN pg_class tbl ON tbl.oid = pgi.indrelid JOIN pg_namespace tnsp ON tnsp.oid = tbl.relnamespace WHERE pgi.indisunique AND tnsp.nspname = 'public'; The output I get is: index_name | index_schema | table_name | table_schema ------------+--------------+------------+-------------- foo_pk | public | foo | public (1 row) I've noted that the documentation states: "Each unique constraint should name a set of columns that is different from the set of columns named by any other unique or primary key constraint defined for the table. (Otherwise, redundant unique constraints will be discarded.)". However, if I create the table like this (again in a fresh database): CREATE TABLE foo ( foo_id int NOT NULL, CONSTRAINT foo_pk PRIMARY KEY (foo_id) ); ALTER TABLE foo ADD CONSTRAINT foo_uq UNIQUE (foo_id); and then again query for the unique indices with the statement above I get: index_name | index_schema | table_name | table_schema ------------+--------------+------------+-------------- foo_pk | public | foo | public foo_uq | public | foo | public (2 rows) There isn't any similar note in the documentation about discarding redundant unique constraints on ALTER, so the documentation is consistent with Postgres' behavior here. That said, I would have expected the ALTER TABLE statement to also drop the redundant index due to the primary key, yielding an identical database schema to the CREATE version. OS info: SELECT version(); version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit (1 row) Note my test database is running under docker, hosted by a 20.04 Ubuntu x86 machine. For some context on why this might matter to a user, I use a SQL generation program (pgmodeler) as the "font of truth" for my schema design. SQL gets exported from the tool and then compared to a series of patch files that should end in an identical schema to the pgmodeler export. I utilize the migra tool to compare differences and raise a build error if any are found. I had inadvertently created a redundant index like the one described above in an earlier patch. Older versions of pgmodeler appear to use the ALTER approach thus creating the redundant index, but the tool now simply uses the CREATE approach which drops the redundant index. When this issue cropped up, I had initially thought it was a migra bug. For my own purposes, simply dropping the redundant constraint was the ultimate solution. I wanted to bring this to the attention of the community if it needed to be addressed further. Perhaps just a note in the documentation would be helpful to someone in the future if it doesn't make sense to change the behavior of ALTER TABLE to match CREATE. Otherwise maybe this mailing list entry can help someone out with a similar issue in the future. Thanks to all of you who work on PostgreSQL; it's an amazing feat of engineering and a pleasure to work with! Thanks, Richard Zuber