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