We have some code which attempts to do a read-modify-write cycle, but the write is an insert if the read failed, as we want to use "lock for update" in postgres. We've discovered that if there's an outer transaction to this code, then we're unable to catch the exception and retry with an update, as the exception is thrown when the transaction is committed, not when the insert happens. Does anyone have any advice?

The insert doesn't happen until the transaction commits

That is what committing means

If you switch to a database that doesn't implement acid transactions as well a postgres (mysql?) that might work