title: The "proper" way to atomically replace all contents in a PostgreSQL table?
body: <p>In the project I have been recently working on, many (PostgreSQL) database tables are just used as big lookup arrays. We have several background worker services, which periodically pull the latest data from a server, then replace all contents of a table with the latest data. The replacing has to be atomic because we don't want a partially completed table to be seen by lookup-ers.</p>
  
  <p>I thought the simplest way to do the replacing is something like this:</p>
  
  <pre><code>BEGIN;
  DELETE FROM some_table;
  COPY some_table FROM 'source file';
  COMMIT;
  </code></pre>
  
  <p>But I found a lot of production code use this method instead:</p>
  
  <pre><code>BEGIN;
  CREATE TABLE some_table_tmp (LIKE some_table);
  COPY some_table_tmp FROM 'source file';
  DROP TABLE some_table;
  ALTER TABLE some_table_tmp RENAME TO some_table;
  COMMIT;
  </code></pre>
  
  <p>(I omit some logic such as change the owner of a sequence, etc.)</p>
  
  <p>I just can't see any advantage of this method. Especially after some discoveries and experiments. SQL statements like <code>ALTER TABLE</code> and <code>DROP TABLE</code> acquire an <a href="https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-TABLES" rel="nofollow noreferrer">ACCESS EXCLUSIVE</a> lock, which even blocks a SELECT.</p>
  
  <p>Can anyone explain what problem the latter SQL pattern is trying to solve? Or it's wrong and we should avoid using it?</p>