I want to replace the entire contents of a table, without affecting any incoming SELECT
statements during the process.
The use case is to have a table which stores mailbox information that is regularly extracted, and needs to be stored in a PostgreSQL table. There are many clients using an application that is constantly querying that same table.
Normally, I would do something like (pseudocode incoming)...
BEGIN TRANSACTION
TRUNCATE TABLE
INSERT INTO
COMMIT
But unfortunately the table cannot be read during this process; due to the time it takes INSERT INTO
to complete. The table is locked.
In MySQL, I would have used their atomic RENAME TABLE
command to avoid these issues...
CREATE TABLE table_new LIKE table;
INSERT INTO table_new;
RENAME TABLE table TO table_old, table_new TO table; *atomic operation*
DROP TABLE table_old;
How could I achieve this in PostgreSQL?
For the purposes of this question, you can assume I am not using foreign keys.
TRUNCATE
command will acquire an AccessExclusive lock on the table, so no one else will be able to read from the table until that transaction commits or is rolled back.delete
instead oftruncate
it will be slower, but without blocking readers. How many rows do you need to delete?DELETE
andINSERT
would be way too long.