Re: Disable duplicate index creation

"Andrus" <kobruleht2(at)hot(dot)ee> writes:
> PostgreSQL runs commands
> create table test ( test integer primary key );
> create index i1 on test(test);
> create index i2 on test(test);

> without any error.
> Now there are 3 same indexes on table.

> How to fix this so that duplicate indexes are not allowed ?

Simple: don't do that.

Having the system try to prevent this has been proposed, and rejected,
before. The main argument against has been that it'd prevent some
occasionally-useful procedures. Instead of REINDEX, which locks out
use of the index (for a long time, if table is big), you can make a new
index in parallel with CREATE INDEX CONCURRENTLY, and then drop the old
index, and not deny service to any query for very long. Another
objection is that it's not always clear which indexes are redundant ---
eg, should we forbid indexes on both (a,b) and (b,a)? In your above
example the indexes aren't even really identical --- the pkey one is
unique and the others not.

regards, tom lane