20.17 PostgreSQL indexes and ExclusionConstraint

PostgresIndex: a method and its parameters

PostgresIndex.create_sql writes what a subclass adds, a method and its storage parameters. It calls PostgresIndex.check_supported, an empty hook no class here overrides; hands Index.create_sql a "USING" clause with the class’s suffix, "btree" for BTreeIndex; and puts what PostgresIndex.get_with_params returns in a "WITH" clause after any "INCLUDE", before a tablespace or a condition. A subclass writes only the parameters given.

For the sailings, a schema editor for PostgreSQL, made with no server behind it, wrote:

BTreeIndex(fields=["vessel"], fillfactor=80, deduplicate_items=False, name="sailing_vessel_btree")
CREATE INDEX "sailing_vessel_btree" ON "charts_sailing" USING btree ("vessel") WITH (fillfactor = 80, deduplicate_items = off)

GistIndex(fields=["window"], include=["vessel"], condition=Q(vessel__startswith="Pilot"), fillfactor=90, name="pilot_windows")
CREATE INDEX "pilot_windows" ON "charts_sailing" USING gist ("window") INCLUDE ("vessel") WITH (fillfactor = 90) WHERE "vessel"::text LIKE 'Pilot%'

Only BrinIndex and BloomIndex check their parameters’ values. Bloom is an extension, which the documentation says to install with BloomExtension. Each index class, and ExclusionConstraint, has the check that django.contrib.postgres is installed (The PostgreSQL application: PostgresConfig, operations and aggregates).

The suffix also ends the name Index.set_name_with_model gives an unnamed index, which may not pass max_name_length, thirty on Index (Constraints and indexes). PostgresIndex.max_name_length is thirty less three plus the length of the class’s suffix: an unnamed SpGistIndex on the sailings’ window is named "charts_sail_window_95d66d_spgist", 32 characters.

OpClass: an operator class for an expression

On PostgreSQL an index may name an operator class for each column or expression it indexes. Over fields, an index or a unique constraint names one for each field with opclasses= (Constraints and indexes). Over expressions, the expression is wrapped in OpClass, a Func that writes it and then the class’s name.

IndexExpression wraps each expression of an index or a unique constraint and settles where OpClass may stand. It lifts its wrapper classes off the top of the expression, puts the rest in parentheses unless it resolves to a column, and nests the wrappers back around it in the order of IndexExpression.wrapper_classes, the first outermost. PostgresConfig.ready makes those OrderBy, OpClass and Collate with IndexExpression.register_wrappers, so that after an index’s column come its collation, its operator class and its direction, in that order.

Index(OpClass(Collate("vessel", "C"), name="varchar_pattern_ops").desc(nulls_last=True), name="sailing_vessel_c")
CREATE INDEX "sailing_vessel_c" ON "charts_sailing" ("vessel" COLLATE "C" varchar_pattern_ops DESC NULLS LAST)

Two of one class raise ValueError, and so does a wrapper below the top: “OrderBy, OpClass, Collate must be topmost expressions in an indexed expression.” Without the application OpClass is no wrapper, and its name goes inside the expression’s parentheses.

In validate, an exclusion or a unique constraint drops an OpClass, whose OpClass.constraint_validation_compatible is false, from the top of an expression: BaseExpression.get_expression_for_validation returns the expression inside, one level only.

ExclusionConstraint

The harbour office’s bookings of one berth may not overlap, cancelled ones aside:

# charts/models.py
class Berthing(models.Model):
    berth = models.CharField(max_length=10)
    vessel = models.CharField(max_length=60)
    stay = DateTimeRangeField()
    cancelled = models.BooleanField(default=False)

    class Meta:
        constraints = [
            ExclusionConstraint(
                name="one_berthing_at_a_time",
                expressions=[("stay", RangeOperators.OVERLAPS), ("berth", RangeOperators.EQUAL)],
                condition=Q(cancelled=False),
                deferrable=Deferrable.DEFERRED,
                include=["vessel"],
            ),
        ]

The expressions are pairs: an expression or a field’s name, and an operator’s text, which RangeOperators spells (PostgreSQL fields: ArrayField, the range fields and hstore). The index type is "gist", "spgist" or "hash" in any case, "GIST" by default. ExclusionConstraint.__init__ checks the index type, the expressions’ shape and the types of the condition, the deferrable and the include, and refuses a hash index with more than one pair, with an include, or with an operator other than RangeOperators.EQUAL.

ExclusionConstraint._get_expressions makes each pair an ExclusionConstraintExpression, an IndexExpression whose template adds "WITH" and the operator, so an expression is wrapped as in an index. ExclusionConstraint.constraint_sql compiles them and the condition as an index’s are, without table names and with values written in by the editor’s quote_value, and asks the editor for the "INCLUDE" and "DEFERRABLE" clauses. ExclusionConstraint.create_sql puts the clause after "ALTER TABLE … ADD"; ExclusionConstraint.remove_sql drops it with BaseDatabaseSchemaEditor.sql_delete_check. For the berths, with no server:

ALTER TABLE "charts_berthing" ADD CONSTRAINT "one_berthing_at_a_time" EXCLUDE USING GIST ("stay" WITH &&, "berth" WITH =) INCLUDE ("vessel") WHERE (NOT "cancelled") DEFERRABLE INITIALLY DEFERRED
ALTER TABLE "charts_berthing" DROP CONSTRAINT "one_berthing_at_a_time"

The documentation asks for the btree_gist extension with GiST. ExclusionConstraint.check checks the fields the expressions name: one over a berth, on the sailings, reported "models.E012".

validate: the rule asked of the database

ExclusionConstraint.validate asks whether a stored row would break the rule with the instance. For each pair it makes a PostgresOperatorLookup with the pair’s operator, the expression on the left and on the right the same expression with the instance’s values, from Model._get_field_expression_map, in place of its fields. It filters the default manager’s rows by them, leaves out a saved instance’s own row, and raises ValidationError with the constraint’s message and code if a row is found. A condition goes through Q.check, which tests it on the instance’s values together with an Exists of those rows that meet it. For a new booking of berth B4, a stand-in for SQLCompiler.execute_sql caught the statement it would have sent:

SELECT %s AS "_check" WHERE (NOT %s AND EXISTS(SELECT %s AS "a" FROM "charts_berthing" "U0" WHERE ("U0"."stay" && %s::tstzrange AND "U0"."berth" = %s AND NOT "U0"."cancelled") LIMIT 1))
params: Int4(1), False, Int4(1), <the stay>, 'B4'

The second parameter is the booking’s cancelled: a cancelled booking finds nothing, and one that is not meets only rows that are not.

validate ignores the index type, the include and the deferrable. When exclude names a field that an expression or the condition uses, it returns without asking: with the stay excluded the stand-in caught nothing. And it works only on PostgreSQL, the one database for which PostgresOperatorLookup writes SQL: on the harbour’s SQLite database it raised NotImplementedError, “Subclasses must implement as_sql()”.