On Thu, Apr 11, 2019 at 9:43 AM Peter Billen <peter.bil...@gmail.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
