In digging around the codebase (see thread: Referential Integrity Checks with Statement-level Triggers), I noticed that unique constraints are similarly enforced with a per-row trigger.
The situation with unique indexes is fairly similar to the situation with RI checks: there is some overhead to using a transition table, but that overhead may be less than the cost of firing a trigger once per row inserted/updated. However, there are some significant differences (apologies to everyone already familiar with this part of the code, it's new to me). For one, there is no analog to RI_Initial_Check(). Instead the constraint is initially checked via building/finding the unique index that would enforce the uniqueness check. Then, the actual lookup done in unique_key_recheck has to contend with the intricacies of HOT updates, so I don't know if that can be expressed in an SPI query. Even if not, I think it should be possible to iterate over the EphemeralNamedRelation and that would result itself have a payoff in reduced trigger calls. I'm going to be working on this as a POC patch separate from the RI work, hence the separate thread, but there's obviously a lot of overlap. All advice is appreciated.
