serializable transaction: exclude constraint violation (backed byGIST index) instead of ssi conflict
От | Peter Billen |
---|---|
Тема | serializable transaction: exclude constraint violation (backed byGIST index) instead of ssi conflict |
Дата | |
Msg-id | CAMTXbE-sq9JoihvG-ccC70jpjMr+DWmnYUj+VdnFRFSRuaaLZQ@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: serializable transaction: exclude constraint violation (backed byGIST index) instead of ssi conflict
|
Список | pgsql-hackers |
Hi all,
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?
Thanks.
В списке pgsql-hackers по дате отправления: