Re: SELECT FOR UPDATE on rows that don't exist

David,
Thank you very much. I missed the advisory locks feature. That feels like
the perfect thing; the BIGINT keys are a bit clunky but that's minor.

Your description of the sequence of events for the two SELECT FOR UPDATE
transactions makes sense. To apply it to my pseudo code, the second
transaction would unblock when the row was deleted in the first rather than
when the first transaction commits or rolls back.

One question though:

You've informed the system you are going to be updating rows on the table
> but as yet have not given it specific rows to protect.
>
>>
>> Without #*1, a simple experiment shows that two processes can be in the
>> critical section at the same time. Add #*1 seems to achieve the desired
>> behavior, but is it really?
>>
>
> The index will not allow duplicates to be inserted and the first one to
> try forces all other potential insertions to wait until the first one
> commits;
>

Is this true in all situations? That is, will an index insertion in one
transaction block the index insertion in another transaction? Presumably
only only if the index entries match, but how does this work in practice?
Does the second transaction block when it sees the conflict? Or does it
proceed on its own version of the index proceeding until it tries to commit?

Thanks again,
Joe