serializable transaction: exclude constraint violation (backed by GIST index) instead of ssi conflict

From: Peter Billen <peter(dot)billen(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: serializable transaction: exclude constraint violation (backed by GIST index) instead of ssi conflict
Date: 2019-04-10 21:43:36
Message-ID: CAMTXbE-sq9JoihvG-ccC70jpjMr+DWmnYUj+VdnFRFSRuaaLZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2019-04-10 21:49:37 finding changed blocks using WAL scanning
Previous Message Isaac Morland 2019-04-10 21:08:16 Re: PostgreSQL pollutes the file system