title: How to prevent phantom reads in Postgres?
body: <p>Is is possible to prevent Phantom reads, or otherwise lock on a missing row in a Postgres transaction?   For example, consider the following sequence of commands:</p>
  
  <p>On connection 1:</p>
  
  <pre><code>CREATE TABLE weather ( city varchar(80) PRIMARY KEY );
  BEGIN;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  INSERT INTO weather VALUES ('a');
  </code></pre>
  
  <p>Meanwhile, on connection 2:</p>
  
  <pre><code>BEGIN;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT * FROM weather WHERE city = 'a' FOR SHARE;
  INSERT INTO weather VALUES ('b');
  </code></pre>
  
  <p>And back to connection 1:</p>
  
  <pre><code>COMMIT;
  </code></pre>
  
  <p>And again back to connection 2:</p>
  
  <pre><code>COMMIT;
  SELECT * FROM weather;
  -- Shows both rows
  </code></pre>
  
  <p>It would seem impossible for the transaction on connection 2 to succeed, because the precondition for creating row 'b' was dependent on the absence of row 'a'.   How do I prevent the second transaction from succeeding?  </p>