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 <pj@illuminatedcomputing.com>
Reviewed-by: Ronan Dunklau <ronan.dunklau@aiven.io>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/ec8b1d9b-502e-d1f8-e909-1bf9dffe6fa5@illuminatedcomputing.com

4 files changed, 123 insertions, 1 deletions

diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index 48997c75f63..073dcc745c4 100644
--- a/contrib/btree_gist/Makefile
+++ b/contrib/btree_gist/Makefile
@@ -38,7 +38,7 @@ PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
 
 REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
         time timetz date interval macaddr macaddr8 inet cidr text varchar char \
-        bytea bit varbit numeric uuid not_equal enum bool
+        bytea bit varbit numeric uuid not_equal enum bool partitions
 
 SHLIB_LINK += $(filter -lm, $(LIBS))
 
diff --git a/contrib/btree_gist/expected/partitions.out b/contrib/btree_gist/expected/partitions.out
new file mode 100644
index 00000000000..5bddb915871
--- /dev/null
+++ b/contrib/btree_gist/expected/partitions.out
@@ -0,0 +1,82 @@
+-- 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;
+ id |        valid_at         
+----+-------------------------
+  1 | [01-01-2000,02-01-2000)
+  1 | [02-01-2000,03-01-2000)
+  2 | [01-01-2000,02-01-2000)
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(6 rows)
+
+select * from parttmp_1_to_10 order by id, valid_at;
+ id |        valid_at         
+----+-------------------------
+  1 | [01-01-2000,02-01-2000)
+  1 | [02-01-2000,03-01-2000)
+  2 | [01-01-2000,02-01-2000)
+(3 rows)
+
+select * from parttmp_11_to_20 order by id, valid_at;
+ id |        valid_at         
+----+-------------------------
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(3 rows)
+
+update parttmp set valid_at = valid_at * '[2000-01-15,2000-02-15)' where id = 1;
+select * from parttmp order by id, valid_at;
+ id |        valid_at         
+----+-------------------------
+  1 | [01-15-2000,02-01-2000)
+  1 | [02-01-2000,02-15-2000)
+  2 | [01-01-2000,02-01-2000)
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(6 rows)
+
+select * from parttmp_1_to_10 order by id, valid_at;
+ id |        valid_at         
+----+-------------------------
+  1 | [01-15-2000,02-01-2000)
+  1 | [02-01-2000,02-15-2000)
+  2 | [01-01-2000,02-01-2000)
+(3 rows)
+
+select * from parttmp_11_to_20 order by id, valid_at;
+ id |        valid_at         
+----+-------------------------
+ 11 | [01-01-2000,02-01-2000)
+ 11 | [02-01-2000,03-01-2000)
+ 12 | [01-01-2000,02-01-2000)
+(3 rows)
+
+-- make sure the excluson constraint excludes:
+insert into parttmp (id, valid_at) values
+  (2, '[2000-01-15, 2000-02-01)');
+ERROR:  conflicting key value violates exclusion constraint "parttmp_1_to_10_id_valid_at_excl"
+DETAIL:  Key (id, valid_at)=(2, [01-15-2000,02-01-2000)) conflicts with existing key (id, valid_at)=(2, [01-01-2000,02-01-2000)).
+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);
+ERROR:  cannot match partition key to index on column "id" using non-equal operator "<>"
diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build
index 5811026301a..087c5b8d4bf 100644
--- a/contrib/btree_gist/meson.build
+++ b/contrib/btree_gist/meson.build
@@ -88,6 +88,7 @@ tests += {
       'not_equal',
       'enum',
       'bool',
+      'partitions',
     ],
   },
 }
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);