Using PostgreSQL database and django-postgres-extra
for creating a table partitioning in Django 4.2 application.
The database settings are
{
"default": {
"ENGINE": "psqlextra.backend",
"NAME": "example1",
"USER": "example1",
"PASSWORD": "example1",
"HOST": "database",
"PORT": "5432",
"ATOMIC_REQUESTS": False,
"AUTOCOMMIT": True,
"CONN_MAX_AGE": 0,
"CONN_HEALTH_CHECKS": False,
"TIME_ZONE": None,
}
}
PSQLEXTRA_PARTITIONING_MANAGER = 'app.partitioning.manager'
and the model to be partitioned
class TrackingData(PostgresPartitionedModel):
id = models.UUIDField(default=uuid.uuid4, primary_key=True, editable=False)
dynamic_url_object_id = models.IntegerField(blank=True, null=True)
ip_address = models.GenericIPAddressField(blank=False)
user_agent = models.TextField(null=True, blank=True)
scan_time = models.DateTimeField(blank=True, default=datetime.now)
created = models.DateTimeField(auto_now_add=True)
modified = models.DateTimeField(auto_now=True)
class Meta:
verbose_name_plural = 'Tracking Data'
class PartitioningMeta:
method = PostgresPartitioningMethod.RANGE
key = ['scan_time']
then generated migration using the command python manage.py pgmakemigrations
but when running python manage.py migrate
, it is giving error
django.db.utils.NotSupportedError: unique constraint on the partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "tracking_trackingdata" lacks column "scan_time" which is part of the partition key.
Also tried the following update in the Meta
class
class Meta:
verbose_name_plural = 'Tracking Data'
constraints = [
models.UniqueConstraint(
fields=['id', 'scan_time'],
name='unique_scan_time_per_partition'
)
]