From 8c852ba9a4347c4778cc610ad5a9cb50ea701b5c Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 12 Jul 2023 09:24:43 +0200 Subject: Allow some exclusion constraints on partitions Previously we only allowed unique B-tree constraints on partitions (and only if the constraint included all the partition keys). But we could allow exclusion constraints with the same restriction. We also require that those columns be compared for equality, not something like &&. Author: Paul A. Jungwirth Reviewed-by: Ronan Dunklau Reviewed-by: Peter Eisentraut Discussion: https://www.postgresql.org/message-id/flat/ec8b1d9b-502e-d1f8-e909-1bf9dffe6fa5@illuminatedcomputing.com --- contrib/btree_gist/sql/partitions.sql | 39 +++++++++++++++++++++++++++++++++++ 1 file changed, 39 insertions(+) create mode 100644 contrib/btree_gist/sql/partitions.sql (limited to 'contrib/btree_gist/sql') diff --git a/contrib/btree_gist/sql/partitions.sql b/contrib/btree_gist/sql/partitions.sql new file mode 100644 index 00000000000..6265c10121f --- /dev/null +++ b/contrib/btree_gist/sql/partitions.sql @@ -0,0 +1,39 @@ +-- Make sure we can create an exclusion constraint +-- across a partitioned table. +-- That code looks at strategy numbers that can differ in regular gist vs btree_gist, +-- so we want to make sure it works here too. +create table parttmp ( + id int, + valid_at daterange, + exclude using gist (id with =, valid_at with &&) +) partition by range (id); + +create table parttmp_1_to_10 partition of parttmp for values from (1) to (10); +create table parttmp_11_to_20 partition of parttmp for values from (11) to (20); + +insert into parttmp (id, valid_at) values + (1, '[2000-01-01, 2000-02-01)'), + (1, '[2000-02-01, 2000-03-01)'), + (2, '[2000-01-01, 2000-02-01)'), + (11, '[2000-01-01, 2000-02-01)'), + (11, '[2000-02-01, 2000-03-01)'), + (12, '[2000-01-01, 2000-02-01)'); + +select * from parttmp order by id, valid_at; +select * from parttmp_1_to_10 order by id, valid_at; +select * from parttmp_11_to_20 order by id, valid_at; + +update parttmp set valid_at = valid_at * '[2000-01-15,2000-02-15)' where id = 1; + +select * from parttmp order by id, valid_at; +select * from parttmp_1_to_10 order by id, valid_at; +select * from parttmp_11_to_20 order by id, valid_at; + +-- make sure the excluson constraint excludes: +insert into parttmp (id, valid_at) values + (2, '[2000-01-15, 2000-02-01)'); + +drop table parttmp; + +-- should fail with a good error message: +create table parttmp (id int, valid_at daterange, exclude using gist (id with <>, valid_at with &&)) partition by range (id); -- cgit v1.2.3