How do I replace a table in Postgres?

Question

Basically I want to do this:

begin;
lock table a;
alter table a rename to b;
alter table a1 rename to a;
drop table b;
commit;

i.e. gain control and replace my old table while no one has access to it.

Answer 1

Simpler:

BEGIN;
DROP TABLE a;
ALTER TABLE a1 RENAME TO a;
COMMIT;

DROP TABLE acquires an ACCESS EXCLUSIVE lock on the table anyway. An explicit LOCK command is no better. And renaming a dead guy is just a waste of time.

You may want to write-lock the old table while preparing the new, to prevent writes in between. Then you'd issue a lock like this earlier in the process:

LOCK TABLE a IN SHARE MODE;

What happens to concurrent transactions trying to access the table? It's not that simple, read this:

• Best way to populate a new column in a large table?

Explains why you may have seen error messages like this:

ERROR:  could not open relation with OID 123456