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

On Thu, Apr 11, 2019 at 9:43 AM Peter Billen <peter(dot)billen(at)gmail(dot)com> wrote:
> I understood that v11 includes predicate locking for gist indexes, as per https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3ad55863e9392bff73377911ebbf9760027ed405.
>
> I tried this in combination with an exclude constraint as following:
>
> drop table if exists t;
> create table t(period tsrange);
> alter table t add constraint bla exclude using gist(period with &&);
> -- t1
> begin transaction isolation level serializable;
> select * from t where period && tsrange(now()::timestamp, now()::timestamp + interval '1 hour');
> insert into t(period) values(tsrange(now()::timestamp, now()::timestamp + interval '1 hour'));
> -- t2
> begin transaction isolation level serializable;
> select * from t where period && tsrange(now()::timestamp, now()::timestamp + interval '1 hour');
> insert into t(period) values(tsrange(now()::timestamp, now()::timestamp + interval '1 hour'));
> -- t1
> commit;
> -- t2
> ERROR: conflicting key value violates exclusion constraint "bla"
> DETAIL: Key (period)=(["2019-04-10 20:59:20.6265","2019-04-10 21:59:20.6265")) conflicts with existing key (period)=(["2019-04-10 20:59:13.332622","2019-04-10 21:59:13.332622")).
>
> 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.
>
> We introduced the same kind of improvement/fix for btree indexes earlier, see https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fcff8a575198478023ada8a48e13b50f70054766. Should this also be applied for (exclude) constraints backed by a gist index (as gist indexes now support predicate locking), or am I creating incorrect assumptions something here?

Hi Peter,

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.

--
Thomas Munro
https://enterprisedb.com