How to prevent phantom reading in Postgres?

Question

Is it possible to prevent Phantom from reading or otherwise block the missing row in a Postgres transaction? For example, consider the following sequence of commands:

When connecting 1:

CREATE TABLE weather ( city varchar(80) PRIMARY KEY ); BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO weather VALUES ('a'); 

Meanwhile, with compound 2:

 BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM weather WHERE city = 'a' FOR SHARE; INSERT INTO weather VALUES ('b'); 

And back to compound 1:

 COMMIT; 

And again, back to compound 2:

 COMMIT; SELECT * FROM weather; -- Shows both rows 

It would seem impossible for the transaction on connection 2 to be successful, since the precondition for creating row "b" depended on the absence of row "a". How to prevent the successful completion of the second transaction?

Answer 1

One way to do this without locking the entire table is to use the Postgresql Advisory lock [1] mechanism:

 -- tx 1 begin; select pg_advisory_lock(1234); insert/update.... commit; -- tx 2 begin; select pg_advisory_lock(1234); SELECT * FROM weather WHERE city = 'a' FOR SHARE; insert/update... commit; 

Thus, you can exchange between transactions where the usual MVCC behavior is not enough. In Example 1234 , an arbitrary integer with a value at the application level. Also see [2] for additional options for working with advisory locks.

[1] http://www.postgresql.org/docs/9.5/static/explicit-locking.html#ADVISORY-LOCKS

[2] http://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS-TABLE