Nice - that's a good case for barriers too. When there's no row to SELECT FOR UPDATE against, you'd inject the barrier after acquiring the advisory lock and verify the second transaction blocks until the first commits.

Seems like a good way to materialize the conflict.

I always did "INSERT ... ON CONFLICT DO NOTHING".

Does that queue when the table is locked? Or just skip writes entirely whenever there is a transaction?

Yes, unless you SET LOCAL lock_timeout = '1ms'; inside the transaction.