CREATE TABLE boom (a integer, b integer); -- index on whole-row expression CREATE UNIQUE INDEX ON boom ((boom));
INSERT INTO boom VALUES (1, 2), (1, 3); ALTER TABLE boom DROP b; TABLE boom; a --- 1 1 (2 rows) REINDEX TABLE boom; ERROR: could not create unique index "boom_boom_idx" DETAIL: Key ((boom.*))=((1)) is duplicated. The problem here is that there *is* a "pg_depend" entry for the index, but it only depends on the whole table, not on specific columns. I have been thinking what would be the right approach to fix this: 1. Don't fix it, because it is an artificial corner case. (But I can imagine someone trying to exclude duplicate rows with a unique index.) 2. Add code that checks if there is an index with a whole-row reference in the definition before dropping a column. That feels like a wart for a special case. 3. Forbid indexes on whole-row expressions. After all, you can do the same with an index on all the columns. That would open the question what to do about upgrading old databases that might have such indexes today. 4. Add dependencies on all columns whenever a whole-row expression is used in an index. That would need special processing for pg_upgrade. I'd like to hear your opinions. Yours, Laurenz Albe
