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.
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 |