I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe.

What would be the ideal way to do this on an active table in production? Wouldn't naively calling alter table foo rename to bar end up blocking newer transactions until the DDL can get its exclusive lock?

Would trying set lock_timeout TO '1s' and attempting the DDL multiple times until it can succeed be a better strategy? Or should I just bite the bullet and take my system offline for a minute or two and get it over with?

  • The problem is that when you get around the access lock you still have the problem of errors with missing table after you renamed it. Oracle has a atomic switch, not sure if PG has one. So maybe downtime is a good solution after all. (Or a application side pool which allows to freeze new dB activity) – eckes Nov 15 '18 at 11:54
  • 1
    @eckes That's a good point. I feel I may need to explain my full strategy here. Both foo and bar exist to begin with. My application is written to look at bar if it can't find what it needs in foo. In a single transaction, rename bar to bar_empty, foo to bar, and bar_empty to foo. Yes, I'm desperate. Downtime is the enemy :) bar will essentially be read-only. foo is always the "active" table. – Ragesh Nov 15 '18 at 12:06
  • @eckes Postgres’s DDL is transactional, and I sure as hell hope their commit; is atomic :-) – Colin 't Hart Nov 15 '18 at 17:54
  • @Colin'tHart ah good point – eckes Nov 15 '18 at 19:51

Your Answer

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

Browse other questions tagged or ask your own question.