search

fordfrog / apgdiff

Another PostgreSQL Diff Tool
http://www.apgdiff.com
MIT License
355 stars 138 forks source link

Rename index constraints #181

Open amirkarimi opened 8 years ago

amirkarimi commented 8 years ago

We are encountering the following error comparing a db with a newer schema which one of it's indexes is renamed.

ERROR:  cannot drop constraint Doctors_pkey on table "Advisors" because other objects depend on it
DETAIL:  constraint fk_AdvisorCustomer_advisorId on table "AdvisorCustomers" depends on index "Doctors_pkey"
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

"Doctors_pkey" is renamed to "Advisors_pkey" in new schema. apgdiff tries to drop the old pkey and create a new one with the new name (instead of just renaming it). But the old one is referenced by other constraints. Why apgdiff doesn't just rename the constraint using the following command:

ALTER INDEX "Doctors_pkey" RENAME TO "Advisors_pkey";
fordfrog commented 8 years ago

the problem is that when index names are different, you'd have to search all indexes specified on the table whether their definition does match and then you'd have to handle the rest of the indexes that did not match anything. this implementation would be much more complicated than simple comparison of index names and their definitions. so unless someone writes the code, index renames won't be supported.

anyway, unless you need to run apgdiff in some sort of unattended batch where you cannot modify the "diff" in the process, you can simply change the DROP/CREATE to ALTER as you mentioned above and it's all you need to do.