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  (Thomas Munro <thomas.munro@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Isaac Morland
Дата:
Сообщение: Re: PostgreSQL pollutes the file system
Следующее
От: Robert Haas
Дата:
Сообщение: finding changed blocks using WAL scanning