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

On Thu, Apr 11, 2019 at 1:14 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Thu, Apr 11, 2019 at 10:54 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
> wrote:
> > On Thu, Apr 11, 2019 at 9:43 AM Peter Billen <peter(dot)billen(at)gmail(dot)com>
> wrote:
> > > I kinda expected/hoped that transaction t2 would get aborted by a
> serialization error, and not an exclude constraint violation. This makes
> the application session bound to transaction t2 failing, as only
> serialization errors are retried.
>
> > Yeah, I agree, the behaviour you are expecting is desirable and we
> > should figure out how to do that. The basic trick for btree unique
> > constraints was to figure out where the index *would* have written, to
> > give the SSI machinery a chance to object to that before raising the
> > UCV. I wonder if we can use the same technique here... at first
> > glance, check_exclusion_or_unique_constraint() is raising the error,
> > but is not index AM specific code, and it is somewhat removed from the
> > GIST code that would do the equivalent
> > CheckForSerializableConflictIn() call. I haven't looked into it
> > properly, but that certainly complicates matters somewhat... Perhaps
> > the index AM would actually need a new entrypoint that could be called
> > before the error is raised, or perhaps there is an easier way.
>
> Adding Kevin (architect of SSI and reviewer/committer of my UCV
> interception patch) and Shubham (author of GIST SSI support) to the CC
> list in case they have thoughts on this.
>

Thanks Thomas, appreciated!

I was fiddling some more, and I am experiencing the same behavior with an
exclude constraint backed by a btree index. I tried as following:

drop table if exists t;
create table t(i int);
alter table t add constraint bla exclude using btree(i with =);

-- t1
begin transaction isolation level serializable;
select * from t where i = 1;
insert into t(i) values(1);

-- t2
begin transaction isolation level serializable;
select * from t where i = 1;
insert into t(i) values(1);

-- t1
commit;

-- t2
ERROR: conflicting key value violates exclusion constraint "bla"
DETAIL: Key (i)=(1) conflicts with existing key (i)=(1).

Looking back, I now believe that
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fcff8a575198478023ada8a48e13b50f70054766
was intended only for *unique* constraints, and not for *exclude*
constraints as well. This is not explicitly mentioned in the commit
message, though only tests for unique constraints are added in that commit.

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.

Thanks.