[Django] #36627: Support PostgreSQL 18+ temporal constraints

[Django]

#36627: Support PostgreSQL 18+ temporal constraints
------------------------------+--------------------------------------------
Reporter: Adam Johnson | Type: Uncategorized
Status: new | Component: contrib.postgres
Version: dev | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
------------------------------+--------------------------------------------
-
[https://www.postgresql.org/about/news/postgresql-18-released-3142/#:~:text=PostgreSQL%2018%20adds%20temporal%20constraints
Release note]
- [https://www.postgresql.org/docs/18/sql-createtable.html#SQL-
CREATETABLE-PARMS-UNIQUE:~:text=If%20the%20WITHOUT%20OVERLAPS%20option
`UNIQUE CONSTRAINT` docs for `WITHOUT OVERLAPS`]
- [https://www.postgresql.org/docs/18/sql-createtable.html#SQL-
CREATETABLE-PARMS-
UNIQUE:~:text=KEY%20%28%20column%5Fname%20%5B%2C%20%2E%2E%2E%20%5D%20%5B%2C%20column%5Fname-,WITHOUT%20OVERLAPS,-%5D%20%29%20%5B%20INCLUDE
`PRIMARY KEY` docs for `WITHOUT OVERLAPS`]
- [https://www.postgresql.org/docs/18/sql-createtable.html#SQL-
CREATETABLE-PARMS-
UNIQUE:~:text=If%20the%20last%20column%20is%20marked%20with%20PERIOD
`FOREIGN KEY` docs for `PERIOD`]

PostgreSQL 18 comes with support for "temporal constraints". These allow
adding a special modifier to the final column in a unique constraint
(`WITHOUT OVERLAPS`), primary key (`WITHOUT OVERLAPS`), or foreign key
(`PERIOD`) which turns that column into a kind of "period specifier". This
allows enforcing the given constraint is valid only for the range of that
period. While the feature is not time-specific, the natural use case is to
enforce uniqueness / validness over time, hence the name.

I propose that we add support for this feature in Django's
`UniqueConstraint`, through some a new expression `WithoutOverlaps` in
`django.contrib.postgres`, which would be used like this:

{{{
from django.db import models
from django.contrib.postgres.constraints import WithoutOverlaps
from django.contrib.postgres.fields import DateTimeRangeField

class Booking(models.Model):
title = models.TextField()
room = models.ForeignKey(Room, on_delete=models.CASCADE)
span = DateTimeRangeField()

class Meta:
constraints = [
models.UniqueConstraint(
"room",
WithoutOverlaps("span"),
name="%(app_label)s_%(class)s_room_span_no_overlaps"
),
]
}}}

This could compile down to SQL like `UNIQUE (room_id, span WITHOUT
OVERLAPS)`.

It may also be possible, but more tricky, to support the other two ways to
specify temporal constraints:

* Primary key support would need to modify or subclass
`CompositePrimaryKey` to support `WithoutOverlaps` for the final column.
* Foreign key support would need to modify or subclass `ForeignObject`,
which is still a private API.
--
Ticket URL: <https://code.djangoproject.com/ticket/36627>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36627: Support PostgreSQL 18+ temporal constraints

----------------------------------+--------------------------------------
Reporter: Adam Johnson | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

----------------------------------+--------------------------------------
Changes (by Simon Charette):

* type: Uncategorized => New feature

Comment:

If we are to accept this ticket I suggest we focus it on the
`UniqueConstraint` case and then consider `CompositePrimaryKey` and
foreign support once #35956 lands.

The hard part here will likely not be to write the `WithoutOverlaps`
expression part but to adjust `UniqueConstraint.validate` to honor it
without coupling `contrib.postgres` and core.

I'll note that `WITHOUT OVERLAPS` is really just SQL sugar over exclusion
constraints which we already support via `ExclusionConstraint` so I'm not
entirely convinced it's worth adding given the `UniqueConstraint.validate`
complexity it would incur.
--
Ticket URL: <https://code.djangoproject.com/ticket/36627#comment:1>

[Django]

#36627: Support PostgreSQL 18+ temporal constraints
----------------------------------+--------------------------------------
Reporter: Adam Johnson | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------

Comment (by Adam Johnson):

Yes, it seems to be PostgreSQL-only. But the SQL syntax *is* much easier
to use, and it would be similarly so within Django models, which should
help with adoption of exclusion/temporal constraints.
--
Ticket URL: <https://code.djangoproject.com/ticket/36627#comment:2>

[Django]

#36627: Support PostgreSQL 18+ temporal constraints
----------------------------------+--------------------------------------
Reporter: Adam Johnson | Owner: (none)

Type: New feature | Status: closed
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: wontfix

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------

Changes (by Jacob Walls):

* resolution: => wontfix
* status: new => closed

Comment:

Thanks all, please continue this discussion at django/new-features. Any
level of effort estimate you would have on `UniqueConstraint.validate` and
Simon's concern about contrib.postgres-specific flavors of
`UniqueConstraint` would be helpful.
--
Ticket URL: <https://code.djangoproject.com/ticket/36627#comment:3>

[Django]

#36627: Support PostgreSQL 18+ temporal constraints
----------------------------------+--------------------------------------
Reporter: Adam Johnson | Owner: (none)
Type: New feature | Status: closed
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------

Changes (by David Sanders):

* cc: David Sanders (added)

Comment:

I haven't seen the new-features thread for this? so adding my 2¢ here for
now...

I'd love to see temporal features be added to Django, I've been toying
with this myself as well.

It should be noted that MariaDB's temporal features are more feature
complete and differ slightly as they tend more towards the SQL standard.
If we do support temporal features, then I'd strongly urge folks to, at
least, be sure to include Maria in design considerations, (otherwise
centre design around this owing to the closeness of the standard?)

https://mariadb.com/docs/server/reference/sql-structure/temporal-tables

Note the key difference is the formation of "periods" from date/timestamp
columns from which the "without overlaps" keys are formed.

{{{
CREATE OR REPLACE TABLE rooms (
room_number INT,
guest_name VARCHAR(255),
checkin DATE,
checkout DATE,
PERIOD FOR p(checkin,checkout),
UNIQUE (room_number, p WITHOUT OVERLAPS)
);
}}}

Adam if you want to create the new-feature I'll gladly upvote it
--
Ticket URL: <https://code.djangoproject.com/ticket/36627#comment:4>