Foreign keys in partitioned models

[keyz182]

I'm trying to create an FK pointing to the pk of a partitioned table. The docs only mention that this isn't supported in Postgres 10.x, so I'm using 13.

I have a pair of test models:

class TestPartitionedTable(PostgresPartitionedModel):
    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["created_on"]

    #id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    somedata = models.CharField(max_length=256, null=True, blank=True)

    created_on = models.DateTimeField(auto_now_add=True)
    updated_on = models.DateTimeField(auto_now=True)


class Thing(models.Model):
    somedata = models.CharField(max_length=256, null=True, blank=True)
    partitioned = models.ForeignKey(TestPartitionedTable, on_delete=models.CASCADE)

I create a migration with python ./manage.py pgmakemigrations. Then try to run migrate but I get:

$  python ./manage.py migrate                                                                                                                                                                                                                                        ✔  TestPartitions  
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, testpartition
Running migrations:
  Applying testpartition.0001_initial...Traceback (most recent call last):
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "testpartition_testpartitionedtable"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/keyz/Code/TestPartitions/./manage.py", line 22, in <module>
    main()
  File "/home/keyz/Code/TestPartitions/./manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/core/management/base.py", line 414, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/core/management/base.py", line 98, in wrapped
    res = handle_func(*args, **kwargs)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 290, in handle
    post_migrate_state = executor.migrate(
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 131, in migrate
    state = self._migrate_all_forwards(
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 163, in _migrate_all_forwards
    state = self.apply_migration(
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    with self.connection.schema_editor(
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 157, in __exit__
    self.execute(sql)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 192, in execute
    cursor.execute(sql, params)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/keyz/Code/TestPartitions/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "testpartition_testpartitionedtable"

My guess at the moment from some poking around is that maybe the FK creation is looking for a unique constraint on the id field, but because it's partitioned, the unique constraint is actually across id and created_on. If I try to create a unique constraint on id it of course complains due to it not including created_on.

Is this actually supported? Have I missed something, or am I doing something wrong? Or is this a bug?

[Photonios]

This is technically supported because PostgreSQL 12 added support for this. Example: https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/

It doesn't quite work because of the SQL Django generates to create the foreign key. Django uses the following to create the foreign key: https://github.com/django/django/blob/d4c5d2b52c897ccc07f04482d3f42f976a79223c/django/db/backends/base/schema.py#L119

It should drop the FOREIGN KEY (%(column)s) part to make it work. Since this isn't supported by django-postgres-extra at the moment, you can work around this in a migration. The migration can be edited to do something like this:

migrations.SeparateDatabaseAndState(
    state_operations=[
        migrations.AddField(
            model_name="mymodel",
            field=models.ForeignKey("mypartitionedmodel", on_delete=models.CASCADE),
        ),
    ],
    database_operations=[
        # Create the field without the db constraint
        migrations.AddField(
            model_name="mymodel",
            field=models.ForeignKey("mypartitionedmodel", on_delete=models.CASCADE, db_constraint=False),
        ),
        # Create the constraint manually
        migrations.RunSQL(
            "ALTER TABLE myapp_mymodel ADD CONSTRAINT <constraint name> REFERENCES mypartitionedmodel",
            "ALTER TABLE myapp_mymodel DROP CONSTRAINT <constraint name>",
        ),
    ],
)