How do I replace a table in Postgres?

sql postgresql concurrency ddl locks
user3416742 picture user3416742 · Sep 4, 2015 · Viewed 13.1k times · Source

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

Erwin Brandstetter picture Erwin Brandstetter · Sep 4, 2015

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:

Explains why you may have seen error messages like this:

ERROR:  could not open relation with OID 123456

Related questions

Return pre-UPDATE column values using SQL only

I posted a related question, but this is another part of my puzzle. I would like to get the OLD value of a column from a row that was UPDATEd - WITHOUT using triggers (nor stored procedures, nor any other …

Read More
Is SELECT or INSERT in a function prone to race conditions?

I wrote a function to create posts for a simple blogging engine: CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[]) RETURNS INTEGER AS $$ DECLARE InsertedPostId INTEGER; TagName VARCHAR; BEGIN INSERT INTO Posts (Title, Body) VALUES ($1, $2) RETURNING Id INTO InsertedPostId; FOREACH TagName IN …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More