From c2f6c05c4cc73e831b7e852eb58bd6d7a83fa46c Mon Sep 17 00:00:00 2001 From: Nick Pope Date: Fri, 28 May 2021 23:56:23 +0100 Subject: Refs #32943 -- Added support for covering exclusion constraints using SP-GiST indexes on PostgreSQL 14+. --- tests/postgres_tests/test_constraints.py | 126 +++++++++++++++++++++++++------ 1 file changed, 104 insertions(+), 22 deletions(-) (limited to 'tests/postgres_tests') diff --git a/tests/postgres_tests/test_constraints.py b/tests/postgres_tests/test_constraints.py index dfe63be61b..bf0b57488a 100644 --- a/tests/postgres_tests/test_constraints.py +++ b/tests/postgres_tests/test_constraints.py @@ -271,16 +271,6 @@ class ExclusionConstraintTests(PostgreSQLTestCase): include='invalid', ) - def test_invalid_include_index_type(self): - msg = 'Covering exclusion constraints only support GiST indexes.' - with self.assertRaisesMessage(ValueError, msg): - ExclusionConstraint( - name='exclude_invalid_index_type', - expressions=[(F('datespan'), RangeOperators.OVERLAPS)], - include=['cancelled'], - index_type='spgist', - ) - def test_invalid_opclasses_type(self): msg = 'ExclusionConstraint.opclasses must be a list or tuple.' with self.assertRaisesMessage(ValueError, msg): @@ -709,14 +699,33 @@ class ExclusionConstraintTests(PostgreSQLTestCase): RangesModel.objects.create(ints=(51, 60)) @skipUnlessDBFeature('supports_covering_gist_indexes') - def test_range_adjacent_include(self): - constraint_name = 'ints_adjacent_include' + def test_range_adjacent_gist_include(self): + constraint_name = 'ints_adjacent_gist_include' self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) constraint = ExclusionConstraint( name=constraint_name, expressions=[('ints', RangeOperators.ADJACENT_TO)], - include=['decimals', 'ints'], index_type='gist', + include=['decimals', 'ints'], + ) + with connection.schema_editor() as editor: + editor.add_constraint(RangesModel, constraint) + self.assertIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + RangesModel.objects.create(ints=(20, 50)) + with self.assertRaises(IntegrityError), transaction.atomic(): + RangesModel.objects.create(ints=(10, 20)) + RangesModel.objects.create(ints=(10, 19)) + RangesModel.objects.create(ints=(51, 60)) + + @skipUnlessDBFeature('supports_covering_spgist_indexes') + def test_range_adjacent_spgist_include(self): + constraint_name = 'ints_adjacent_spgist_include' + self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + constraint = ExclusionConstraint( + name=constraint_name, + expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='spgist', + include=['decimals', 'ints'], ) with connection.schema_editor() as editor: editor.add_constraint(RangesModel, constraint) @@ -728,12 +737,28 @@ class ExclusionConstraintTests(PostgreSQLTestCase): RangesModel.objects.create(ints=(51, 60)) @skipUnlessDBFeature('supports_covering_gist_indexes') - def test_range_adjacent_include_condition(self): - constraint_name = 'ints_adjacent_include_condition' + def test_range_adjacent_gist_include_condition(self): + constraint_name = 'ints_adjacent_gist_include_condition' self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) constraint = ExclusionConstraint( name=constraint_name, expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='gist', + include=['decimals'], + condition=Q(id__gte=100), + ) + with connection.schema_editor() as editor: + editor.add_constraint(RangesModel, constraint) + self.assertIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + + @skipUnlessDBFeature('supports_covering_spgist_indexes') + def test_range_adjacent_spgist_include_condition(self): + constraint_name = 'ints_adjacent_spgist_include_condition' + self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + constraint = ExclusionConstraint( + name=constraint_name, + expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='spgist', include=['decimals'], condition=Q(id__gte=100), ) @@ -742,12 +767,28 @@ class ExclusionConstraintTests(PostgreSQLTestCase): self.assertIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) @skipUnlessDBFeature('supports_covering_gist_indexes') - def test_range_adjacent_include_deferrable(self): - constraint_name = 'ints_adjacent_include_deferrable' + def test_range_adjacent_gist_include_deferrable(self): + constraint_name = 'ints_adjacent_gist_include_deferrable' + self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + constraint = ExclusionConstraint( + name=constraint_name, + expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='gist', + include=['decimals'], + deferrable=Deferrable.DEFERRED, + ) + with connection.schema_editor() as editor: + editor.add_constraint(RangesModel, constraint) + self.assertIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + + @skipUnlessDBFeature('supports_covering_spgist_indexes') + def test_range_adjacent_spgist_include_deferrable(self): + constraint_name = 'ints_adjacent_spgist_include_deferrable' self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) constraint = ExclusionConstraint( name=constraint_name, expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='spgist', include=['decimals'], deferrable=Deferrable.DEFERRED, ) @@ -755,14 +796,18 @@ class ExclusionConstraintTests(PostgreSQLTestCase): editor.add_constraint(RangesModel, constraint) self.assertIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) - def test_include_not_supported(self): - constraint_name = 'ints_adjacent_include_not_supported' + def test_gist_include_not_supported(self): + constraint_name = 'ints_adjacent_gist_include_not_supported' constraint = ExclusionConstraint( name=constraint_name, expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='gist', include=['id'], ) - msg = 'Covering exclusion constraints require PostgreSQL 12+.' + msg = ( + 'Covering exclusion constraints using a GiST index require ' + 'PostgreSQL 12+.' + ) with connection.schema_editor() as editor: with mock.patch( 'django.db.backends.postgresql.features.DatabaseFeatures.supports_covering_gist_indexes', @@ -771,6 +816,27 @@ class ExclusionConstraintTests(PostgreSQLTestCase): with self.assertRaisesMessage(NotSupportedError, msg): editor.add_constraint(RangesModel, constraint) + def test_spgist_include_not_supported(self): + constraint_name = 'ints_adjacent_spgist_include_not_supported' + constraint = ExclusionConstraint( + name=constraint_name, + expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='spgist', + include=['id'], + ) + msg = ( + 'Covering exclusion constraints using an SP-GiST index require ' + 'PostgreSQL 14+.' + ) + with connection.schema_editor() as editor: + with mock.patch( + 'django.db.backends.postgresql.features.DatabaseFeatures.' + 'supports_covering_spgist_indexes', + False, + ): + with self.assertRaisesMessage(NotSupportedError, msg): + editor.add_constraint(RangesModel, constraint) + def test_range_adjacent_opclasses(self): constraint_name = 'ints_adjacent_opclasses' self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) @@ -819,12 +885,28 @@ class ExclusionConstraintTests(PostgreSQLTestCase): self.assertIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) @skipUnlessDBFeature('supports_covering_gist_indexes') - def test_range_adjacent_opclasses_include(self): - constraint_name = 'ints_adjacent_opclasses_include' + def test_range_adjacent_gist_opclasses_include(self): + constraint_name = 'ints_adjacent_gist_opclasses_include' + self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + constraint = ExclusionConstraint( + name=constraint_name, + expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='gist', + opclasses=['range_ops'], + include=['decimals'], + ) + with connection.schema_editor() as editor: + editor.add_constraint(RangesModel, constraint) + self.assertIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) + + @skipUnlessDBFeature('supports_covering_spgist_indexes') + def test_range_adjacent_spgist_opclasses_include(self): + constraint_name = 'ints_adjacent_spgist_opclasses_include' self.assertNotIn(constraint_name, self.get_constraints(RangesModel._meta.db_table)) constraint = ExclusionConstraint( name=constraint_name, expressions=[('ints', RangeOperators.ADJACENT_TO)], + index_type='spgist', opclasses=['range_ops'], include=['decimals'], ) -- cgit v1.3