2

I'm trying to optimize the structure of a table. To do so, I've put the columns in the most optimal order possible and then ran this query (simplified):

INSERT INTO table_new ("id", "some_field", "some_other_field") SELECT "id", "some_field", "some_other_field" FROM table_current;

Then I simply rename the table and add all the same indexes and foreign keys.

ALTER TABLE "table_current" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table_current";
ALTER TABLE "table_current" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;

This has worked great in the past, but in this particular case, there are a LOT of other tables with cascading foreign keys on this table.

So if I recreate and rename, then all the other tables still reference table_old in their foreign keys instead of the new table_current.

How do I do such a table migration like this without having to recreate the foreign keys on every other table as well?

  • There's nothing in pg_dump to help with this really. You'd need to write a query against pg_depend to find all the dependent objects then list them in a pg_dump table-list. – Craig Ringer Jan 26 '17 at 3:47
1

There is no easy way to work around the problem. When you drop columns that other things are referencing, like views or foreign keys, you must manually inform all areas of the schema that have dependencies on that column.

There is no shortcut that I know of around this. No one really makes a habit of reordering tables. Most of the requests are for cosmetic reasons, however if you're really doing it for a valid reason (like table packing) then I wish you the best of luck.

It's been a feature request for a while, and it comes up frequently.

  • Yeah, I've seen the requests. No one has taken up the challenge yet, unfortunately. :( – eComEvo Jan 26 '17 at 23:17
  • That's because the ideal solution is for physical and logical table ordering to be distinct and for physical ordering to be done internally based on packing. That's a huge job.. – Evan Carroll Jan 26 '17 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.