Is it a common problem that duplicate constraints or indexes get created? I have seen this happen in different ways. You run scripts that you think are idempotent, but they are not really in this sense. (It's in the details:
CREATE TABLE foo (a REFERENCES b ...) will fail cleanly when run twice. CREATE TABLE foo (a); ALTER TABLE foo ADD FOREIGN KEY ... will not. See pg_dump.) Or logically duplicate things are created in independent ways, because say the psql \d output isn't clear enough or isn't checked. Obviously, all of these are human errors in some way.I'm thinking that a setting to notify the user about this could be useful. It could be a warning or even an error. I can't see any possible reason in the normal course of use that someone would want two foreign keys that point exactly the same way, or two check constraints that check exactly the same thing, or two indexes that are set up in exactly the same way.
Comments? Experiences?
Posts
Letting the database name your indexes/constraints/etc is a practice I prohibit in my company's Database Coding Standard, for this reason.
ReplyDeleteConsider replacing an index on the fly. Standard practice is to create the new one using CONCURRENTLY, then drop the old. Admittedly we might not need that if we had REINDEX CONCURRENTLY, but it's still true that this shouldn't be an error condition.
ReplyDeletedepending on how it generates names (can it generate more than one name per idx? I assume it does something like col1_col2_col3_idx but if you put a 0 or a 1 on the end of that it could generate 2 idxs for the same thing...) it'll probably lessen the problem since people will hopefully not be naming their own as much and thus they won't generate the same idx twice. At least I hope that's how it works.
ReplyDeleteconstraint wise my biggest annoyance is I can't build a generic constraint. I have at least 1 constraint that I use on several tables (wrote a function and call that from check) but it'd be nice if I could just have a single named constraint instead, and just apply it to columns.
I've seen lots of duplicate indexes out in the wild, and usually nobody knows how things got that way. In the PostgreSQL 7 days I think it happened sometimes during upgrades as old indexes were created and duplicate ones created for primary keys. But I don't think that accounts for all the duplicates I've seen.
ReplyDeleteI think it would be great to disallow creation of a duplicate index. I'd say make the attempt a harmless no-op that generates a warning, though that could be confusing since there would not be an index with the expected name afterwards.
Generating an error would probably be the best, though it'd be a behavioral change that would probably affect someone or other. (But wouldn't they thank you for keeping them from wasting I/O and disk space on duplicate indices?!)
Same things with a duplicate foreign key declaration.
And with a constraint, if it's exactly identical.
Voting for an error on duplicate object creation unless CONCURRENTLY is used.
ReplyDeleteMaybe some Message that the index created, actualy already exists another index over the same columns.
ReplyDeletePostgres should indicate the similiar index, helping to decide which index to erase or else
not allow you to create a new one, if already existe another that makes the same function.
Perhaps a plpgsql function to find (and possibly drop) duplicated indexes would do the trick? This could provide the needed functionality (getting rid of duplicates) without complicating the backend.
ReplyDeleteAlso, I believe that another well known RDBMS automatically makes use of unique indexes if they already exist when creating unique constraints. This seems quite reasonable, really.
I thought there were some technical limitations that were preventing us from handling this situation more cleanly. But I agree that it would be nice to do so.
ReplyDelete