I want to do the following:
BEGIN;
SELECT ... FROM table WHERE a = 1 FOR UPDATE;
UPDATE table SET ... WHERE a = 1;
if that resturns zero then
INSERT INTO table (...) VALUES (...);
END;
The problem is that I need to avoid race conditions. Sometimes I get
primary key exceptions on the INSERT.
I think I need to lock the table in share mode to keep inserts from
happening, but that blocks vacuums, and blocked vacuums block other
things behind them. So how do I get around this?