Re: serializable transaction: exclude constraint violation (backed byGIST index) instead of ssi conflict
| От | Peter Billen |
|---|---|
| Тема | Re: serializable transaction: exclude constraint violation (backed byGIST index) instead of ssi conflict |
| Дата | |
| Msg-id | CAMTXbE-5QMTemd618HgM4wK9pzBVWSCH_uvz4-RA-RhHC57Uxg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: serializable transaction: exclude constraint violation (backed byGIST index) instead of ssi conflict (Thomas Munro <thomas.munro@gmail.com>) |
| Ответы |
Re: serializable transaction: exclude constraint violation (backed byGIST index) instead of ssi conflict
(Peter Billen <peter.billen@gmail.com>)
|
| Список | pgsql-hackers |
On Thu, Apr 11, 2019 at 1:14 AM Thomas Munro <thomas.munro@gmail.com> wrote:
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).
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.
В списке pgsql-hackers по дате отправления: