Re: serializable transaction: exclude constraint violation (backed by GIST index) instead of ssi conflict

On Fri, Apr 12, 2019 at 6:01 AM Peter Billen <peter(dot)billen(at)gmail(dot)com> wrote:
> On Thu, Apr 11, 2019 at 6:12 PM Peter Billen <peter(dot)billen(at)gmail(dot)com> wrote:
>> I believe we are after multiple issues/improvements:
>>
>> 1. Could we extend https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fcff8a575198478023ada8a48e13b50f70054766 by adding support for exclude constraints?
>> 2. Fully support gist & constraints in serializable transactions. I did not yet test a unique constraint backed by a gist constraint, which is also interesting to test I assume. This test would tell us if there currently is a status quo between btree and gist indexes.
>
> Regarding the remark in (2), I forgot that a unique constraint cannot be backed by a gist index, so forget the test I mentioned.

Yeah, well we can't directly extend the existing work because unique
constraints are *entirely* handled inside the btree code (in fact no
other index types even support unique constraints, yet). This
exclusion constraints stuff is handled differently: the error handling
you're seeing is raised by generic code in
src/backend/executor/execIndexing.c , but the code that knows how to
actually perform the necessary SSI checks is index-specific, in this
case in gist.c. To do the moral equivalent of the UCV change, we'll
need to get these two bits of code to communicate across the "index
AM" boundary (the way that index implementations such as GIST are
plugged into Postgres). The question is how.

One (bad) idea is that we could actually perform the (illegal)
aminsert just before we raise that error! We know we're going to roll
back anyway, because that's either going to fail when gist.c calls
CheckForSerializableConflictIn(), or if not, when we raise
"conflicting key value violates exclusion constraint ...". That's a
bit messy though, because it modifies the index unnecessarily and
possibly breaks important invariants. An improved version of that
idea is to add a new optional index AM interface "amcheckinsert()"
that shares whatever code it needs to share to do all the work that
insert would do except the actual modification. That way,
check_exclusion_or_unique_constraint() would give every index AM a
chance to raise an SSI error if it wants to. This seems like it
should work, but I don't want to propose messing around with the index
AM interface lightly. It wouldn't usually get called, just in the
error path.

Anyone got a better idea?

--
Thomas Munro
https://enterprisedb.com