On Thu, Apr 11, 2019 at 10:54 AM Thomas Munro <thomas.munro@gmail.com> wrote: > On Thu, Apr 11, 2019 at 9:43 AM Peter Billen <peter.billen@gmail.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).
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.
