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.

  • Why do you think that the table cannot be read while inserting rows in it? Truncate table will have immediate effect across all sessions; however, inserts (if done inside a transaction that wraps them all, as your pseudo-code suggests) will not be visible to other sessions until you commit. Other sessions will be able to select from the table, and will see an empty table until you commit. – zgguy May 6 '15 at 17:29
  • 1
    @zgguy the 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. – Josh Kupershmidt May 6 '15 at 18:06
  • 2
    If you use delete instead of truncate it will be slower, but without blocking readers. How many rows do you need to delete? – a_horse_with_no_name May 6 '15 at 19:42
  • @a_horse_with_no_name Usually between 200-300k rows with many varchar columns. The wait time of DELETE and INSERT would be way too long. – Clarkey May 7 '15 at 9:49
up vote 15 down vote accepted

Right, the TRUNCATE TABLE command you are performing "... acquires an ACCESS EXCLUSIVE lock on each table it operates on", so in the first SQL block you posted, any other clients attempting to access the table after that time will be blocked until your INSERT finishes and you COMMIT.

You can use the same workaround as in your MySQL-specific code; Postgres supports roughly the same syntax and will have similar locking behavior. To wit:

BEGIN;
-- You probably want to make sure that no one else is
-- INSERT / UPDATE / DELETE'ing from the original table, otherwise
-- those changes may be lost during this switchover process. One way
-- to do that would be via:
-- LOCK TABLE "table" IN ROW EXCLUSIVE mode;
CREATE TABLE "table_new" (LIKE "table");
INSERT INTO "table_new" ...;

-- The ALTER TABLE ... RENAME TO command takes an Access Exclusive lock on "table",
-- but these final few statements should be fast.
ALTER TABLE "table" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table";
DROP TABLE "table_old";

COMMIT;

Extra bonus: Postgres actually supports transactional DDL, unlike MySQL, so in case you need to ROLLBACK the above transaction, you may safely do so.

  • I'm going to do some testing on this, thanks for your answer. If I used the LOCK TABLE method that you suggested, would I need to unlock it again before the COMMIT, or will it unlock itself? – Clarkey May 7 '15 at 9:51
  • EDIT: Found the following statment in this documentation: "There is no UNLOCK TABLE command; locks are always released at transaction end." – Clarkey May 7 '15 at 13:11

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.