Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
> Is it technically possible for there to be a "select for insert"? e.g.
> other select for inserts with the same effective where clause will block
> even if no rows are there yet.
What would you define as the "same effective where clause"? Shades of
the halting problem, I think :-(.
I'd recommend grabbing a table-level EXCLUSIVE MODE lock, which will
allow reads to proceed but lock out other updaters.
Alternatively, consider whether you can't rely on a unique index to
prevent multiple processes from inserting the "same" not-there-yet row.
regards, tom lane