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>",
        ),
    ],
)

[anuj-scanova]

@keyz182 Were you able to successfully execute table partitioning? I'm also facing the same issue but not with the foreign key, instead the partition table itself #210

[keyz182]

I was not I'm afraid. We didn't need partitioning for our workload in the end so we moved on.

[anuj-scanova]

Okay Thanks for the update @keyz182, we need partitioning in our case, but it seems the documentation is not clear and needs to fight a little to get it work.

[Toruitas]

I unfortunately haven't been successful in getting foreign keys to a partition table to work either.

Note that here I'm trying to have a partitioned model have a foreign key to a second partitioned model. Both have unique id fields as the primary key, and use range partitioning on created_at.

Examples of what I've tried:

This produces a syntax error since you need a type of reference: REFERENCE [some type]

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 foreignmodel",
        ),
    ],
)

Using just SQL ends up with this error:
psycopg2.errors.InvalidForeignKey: number of referencing and referenced columns for foreign key disagree

migrations.SeparateDatabaseAndState(
      state_operations=[
          migrations.AddField(
              model_name='mymodel',
              name='my_field',
              field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='my_related_name', to='app.foreign_model'),
          ),
      ],
      database_operations=[
          migrations.RunSQL(
              "ALTER TABLE app_mymodel ADD COLUMN foreignmodel_id bigint NOT NULL CONSTRAINT <constraint name> REFERENCES foreigntable_foreignmodel(id, created_at) ON DELETE cascade DEFERRABLE INITIALLY DEFERRED", 
          ),
      ],  
  ),

I'd still prefer to use foreign keys, but for now it looks like I'll have to just store the id as a regular old integer and take care of data integrity in some other way. So if there is actually a way to pull this off, I'm happy to try again.

[gabriel-v]

@Toruitas

psycopg2.errors.InvalidForeignKey: number of referencing and referenced columns for foreign key disagree

That's a legit error, you need 2 columns for a FK that references 2 columns. So you will need to add two fields, then separately issue the FK:

CREATE TABLE words_payments (
        sid     text        NOT NULL,
        social  integer     NOT NULL ... ,
        foreign key (sid, social) references words_social  (sid, social)
);

https://www.postgresql.org/message-id/24581.1501335701%40sss.pgh.pa.us

Now the question is how to get a single name into Django ORM for the referred object - until then, you'd have to change the object.foreign into models.Foreign.objects.get(a=object.foreign_a, b=object.foreign_b) -- I think a @property on the model will fool it for direct access, but the ORM also allows access through objects.filter('object__foreign__property') and those will not work anymore, so you will also want to write a custom lookup

[gabriel-v]

Here is my working example.

Initially I had this:

1533     # prev = models.ForeignKey(  
1534     #     Task,                              
1535     #     on_delete=models.CASCADE,                                                          
1536     #     related_name='next_set',                                                        
1537     # )                      
1538     # """the task needed by another task"""                                                                    
1539                                  
1540     # next = models.ForeignKey(                           
1541     #     Task,
1542     #     on_delete=models.CASCADE,
1543     #     related_name='prev_set',
1544     # )
1545     # """ the task that depends on `prev`"""

Then I partitioned Task by (func, args) to obtain TaskPartitioned.

Now I have these:

1528     prev_func = models.CharField(max_length=1024)
1529     prev_args = JSONField()                                                               
1530     next_func = models.CharField(max_length=1024) 
1531     next_args = JSONField()
1532
1533     @property                    
1534     def next(self):                                     
1535         ...                                                                                  
1536                                                                                           
1537     @property                      
1538     def prev(self):                                                                                            
1539         ... 

And on the other table, @property next_set and @property prev_set to keep the duck happy.

SQL migrations:

    operations = [    
        # Create the constraint manually    
        migrations.RunSQL(    
            """ALTER TABLE data_taskdependencypartitioned    
            ADD CONSTRAINT data_taskdependencypartitioned_fk_next    
            FOREIGN KEY (next_func, next_args)                                                 
            REFERENCES data_taskpartitioned (func, args)    
            ON DELETE CASCADE                                                                                       
            """,                                                                                                      
            """ALTER TABLE data_taskdependencypartitioned                                                          
            DROP CONSTRAINT data_taskdependencypartitioned_fk_next""",    
        ),                                                           
        migrations.RunSQL(                                                                     
            """ALTER TABLE data_taskdependencypartitioned                                      
            ADD CONSTRAINT data_taskdependencypartitioned_fk_prev                                                   
            FOREIGN KEY (prev_func, prev_args)                                                                      
            REFERENCES data_taskpartitioned (func, args)                                                                
            ON DELETE CASCADE                                             
            """,                                                          
            """ALTER TABLE data_taskdependencypartitioned                 
            DROP CONSTRAINT data_taskdependencypartitioned_fk_prev""",    
        ),    
    ]    

I'll post the custom lookup if i get it working. And then maybe we can see if we could wrap all of this boilerplate under some custom ForeignKey field with all the magic under this library, like the custom UniqueIndex

---

Edit:

Adding a FK like this will prevent you from deleting the partitions. When trying to revert a "AddPartitions" transaction that came after the FK above, I get this error:

django.db.utils.InternalError: cannot drop table data_taskpartitioned_pt_func_args_24_of_24 because other objects depend on it
DETAIL:  constraint data_taskdependencypartitioned_fk_next on table data_taskdependencypartitioned depends on table data_taskpartitioned_pt_func_args_24_of_24
constraint data_taskdependencypartitioned_fk_prev on table data_taskdependencypartitioned depends on table data_taskpartitioned_pt_func_args_24_of_24
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

So this means the "AddPartition" transaction needs to happen before creation of the FK - and also means you can't dynamically delete any partitions after FK creation. This might be a problem with rolling timestamp RANGE partitions

[Toruitas]

That's great to see! I reckon the approach mentioned here for truncating a table would also work for this use case. Specifically, the first comment by Laurenz about dropping the constraint, deleting or detaching the table or partition, then re-creating the constraint it if needed.

[rpradal]

@Photonios what is the update about this ?
The SQL you put in your answer does not seem to be a valid sql statement so the workaround won't work for us.

[Toruitas]

@gabriel-v 's code does the trick, I was able to use a variation of it to get FKs to datetime range partitioned table data. I havent yet attempted a custom lookup.

At the risk of repetition, here's what I did:

# Generated by Django 3.2.21 on 2023-09-13 16:03

from django.db import migrations



class Migration(migrations.Migration):

    dependencies = [
        ('app', '0002_add_historical_partitions_thing'),
    ]

    operations = [    
        # Create the FK constraint manually    
        migrations.RunSQL(    
            """ALTER TABLE app_model
            ADD CONSTRAINT app_model_fk_model2
            FOREIGN KEY (model2_id, model2_created_at)
            REFERENCES app_model2 (id, created_at)
            ON DELETE CASCADE
            """,
            """ALTER TABLE app_model
            DROP CONSTRAINT app_model_fk_model2""",
        ),
    ]

Then the model itself:

class Model(PartitionedMixin):
    model2_id = models.BigIntegerField(null=True, blank=True)
    model2_created_at = models.DateTimeField(null=True, blank=True)

    @property
    def model2(self):
        try:
            return Model2.objects.get(id=self.model2_id, created_at=self.model2_created_at)
        except Model2.DoesNotExist:
            return None

[rpradal]

Ok i see thanks. I am not a big fan of this solution since the relationship between the two tables only appears in the postgresql constraint and not in the django code itself.
That's too bad that django-composite-foreignkey is not maintained anymore.

For my use case, i think that i will go for a solution using ForeignKey with db_constraint=False and maintain the data integrity myself. This is not ideal either but will be manageable for my use case