Postgresql - How to safely rename table

Question

In PostgreSQL 13, I am loading data to a Staging table which has the same schema structure as the old table i.e. Target table and once all rows are loaded I am Dropping the old table and renaming Staging to the old table name.

The above works, But my concern is even if the table rename command should not take a lot of time still the worst-case scenario, What if someone tries to query the old table at the same time while it was executing the DROP old table query? my understanding is their application will fail basically some sort of table lock issue.

So how can we avoid that or how can we safely rename/switch the table without such issues?

Thanks!

UPDATE

In MS SQL Server we can do table switching instead of renaming tables which is explained very well in the below link. So do we have similar option in Postgresql as well?

https://littlekendra.com/2017/01/19/why-you-should-switch-in-staging-tables-instead-of-renaming/

Do you need to replace everything in target table or are you just inserting/updating/deleting some values? — Adrian Klaver
– Adrian Klaver, Commented Jan 23, 2023 at 18:00
@AdrianKlaver I would prefer Truncating all the data of the target table and then loading everything to it from Staging table, without any locks issue and at same time Target tablet being empty. — Vikas J
– Vikas J, Commented Jan 23, 2023 at 18:39
The process you describe in your comment is different from that in your question. Which is the one you are trying to achieve? — Adrian Klaver
– Adrian Klaver, Commented Jan 23, 2023 at 20:26

Laurenz Albe · Accepted Answer · 2023-01-24 18:45:27Z

4

You can simply do it like this in a single transaction:

START TRANSACTION;

/* this will block until the last concurrent transaction
   that uses the table is done */
DROP TABLE tab;

ALTER TABLE tab_new RENAME TO tab;

COMMIT;

A concurrent transaction that tries to access the table will have to wait until the transaction is done, whereupon it will use the new table. There should be no errors, and the only disruption can happen if a long running concurrent transaction blocks the DROP TABLE for a long time.

If you want to avoid blocking all concurrent statements for a longer time, you can add the following after the start of the transaction:

SET LOCAL lock_timeout = '500ms';

That will cancel the DROP TABLE and abort the containing transaction if the lock is not granted withing half a second. Then you retry the transaction after a randomized delay and keep repeating until it succeeds.

edited Jan 24, 2023 at 18:45

answered Jan 23, 2023 at 19:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vikas J Over a year ago

Thanks, @Laurenz Albe But is there anything like the below link in Postgresql for switching data from 1 table to another instead of renaming? littlekendra.com/2017/01/19/…

Laurenz Albe Over a year ago

I have added instructions for a (slightly less comfortable) possible solution.

Collectives™ on Stack Overflow

Postgresql - How to safely rename table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related