Martijn van Oosterhout <kleptog@svana.org> writes:
> check_unique_index( ctid of inserting tuple, ctid of possibly
> conflicting tuple)
I agree it's pretty ugly to have the index AM directly poking into
the heap, but adding a level of subroutine doesn't really make that
a whole lot nicer :-(.
In any case, you've underestimated the amount of coupling here: if
the conflicting tuple is dead, _bt_check_unique also wants to know
just how dead it is, so it can possibly set LP_DELETE on the old index
entry.
> Now, one side-effect is that you could build deferrable unique
> constraints on top of this by having the check function always return
> InvalidTransactionId but storing the conflicts for later checking.
I think this is not as easy as all that; consider race conditions
against VACUUM for instance (the tuples might not be there anymore
when you want to check the conflict). Also, we really do want to go
back and set LP_DELETE if the conflict tuple is sufficiently dead.
Having that not happen is unappetizing, because you could end up
repeating the check a large number of times over successive updates.
N updates will take O(N^2) time.
My own thoughts about deferred unique checks have been along the lines
of storing the possibly-conflicting key value when the initial check
notes a problem, and then repeating the index search at commit.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
