Getting Django migration error "sequence must have same owner as table it is linked to", but all tables and sequences have the same owner

Question

I'm trying to run a migration in a Django project. (Django 3.1, Python 3.9.9) I'm in my virtual environment. I keep getting a puzzling error.

 python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, intake, sessions
Running migrations:
  Applying intake.0021_auto_20220115_1147...Traceback (most recent call last):
  File "/Users/me/Sites/client/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ObjectNotInPrerequisiteState: sequence must have same owner as table it is linked to

But when I list out my tables and sequences, they all have the same owner.

intake=# \dt
                    List of relations
 Schema |            Name            | Type  |   Owner   
--------+----------------------------+-------+-----------
 public | auth_group                 | table | dbuser
 public | auth_group_permissions     | table | dbuser
 public | auth_permission            | table | dbuser
 public | auth_user                  | table | dbuser
 public | auth_user_groups           | table | dbuser
 public | auth_user_user_permissions | table | dbuser
 public | django_admin_log           | table | dbuser
 public | django_content_type        | table | dbuser
 public | django_migrations          | table | dbuser
 public | django_session             | table | dbuser
 public | intake_byattorney          | table | dbuser
 public | intake_client              | table | dbuser
(12 rows)

intake=# \ds
                         List of relations
 Schema |               Name                |   Type   |   Owner   
--------+-----------------------------------+----------+-----------
 public | auth_group_id_seq                 | sequence | dbuser
 public | auth_group_permissions_id_seq     | sequence | dbuser
 public | auth_permission_id_seq            | sequence | dbuser
 public | auth_user_groups_id_seq           | sequence | dbuser
 public | auth_user_id_seq                  | sequence | dbuser
 public | auth_user_user_permissions_id_seq | sequence | dbuser
 public | django_admin_log_id_seq           | sequence | dbuser
 public | django_content_type_id_seq        | sequence | dbuser
 public | django_migrations_id_seq          | sequence | dbuser
 public | intake_byattorney_id_seq          | sequence | dbuser
 public | intake_client_id_seq              | sequence | dbuser
(11 rows)

Why might this be? Do I need to change the ownership of the tables and sequences to something else, like postgres?

This is happening locally and on my production machine.

Here is the content of the migration in question:

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('intake', '0020_alter_client_status'),
    ]

    operations = [
        migrations.AlterField(
            model_name='byattorney',
            name='id',
            field=models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID'),
        ),
        migrations.AlterField(
            model_name='client',
            name='id',
            field=models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID'),
        ),
        migrations.AlterField(
            model_name='client',
            name='status',
            field=models.CharField(choices=[('1__intake', 'Stage 1: Needs initial contact'), ('2__attorney-review', 'Stage 2: Needs attorney contact'), ('3__evaluation', 'Stage 3: Evaluation - Accept/Reject'), ('4__final-disposition', 'Stage 4: Final Disposition'), ('5__client-accepted', 'Client Accepted'), ('6__client-rejected', 'Client Rejected')], max_length=20),
        ),
    ]

Here is the output of `python manage.py sqlmigrate intake 0021':

python manage.py sqlmigrate intake 0021
BEGIN;
--
-- Alter field id on byattorney
--
SET CONSTRAINTS "intake_client_by_attorney_id_680583a2_fk" IMMEDIATE; ALTER TABLE "intake_client" DROP CONSTRAINT "intake_client_by_attorney_id_680583a2_fk";
ALTER TABLE "intake_byattorney" ALTER COLUMN "id" TYPE integer USING "id"::integer;
DROP SEQUENCE IF EXISTS "intake_byattorney_id_seq" CASCADE;
CREATE SEQUENCE "intake_byattorney_id_seq";
ALTER TABLE "intake_byattorney" ALTER COLUMN "id" SET DEFAULT nextval('"intake_byattorney_id_seq"');
SELECT setval('"intake_byattorney_id_seq"', MAX("id")) FROM "intake_byattorney";
ALTER SEQUENCE "intake_byattorney_id_seq" OWNED BY "intake_byattorney"."id";
ALTER TABLE "intake_client" ALTER COLUMN "by_attorney_id" TYPE integer USING "by_attorney_id"::integer;
ALTER TABLE "intake_client" ADD CONSTRAINT "intake_client_by_attorney_id_680583a2_fk" FOREIGN KEY ("by_attorney_id") REFERENCES "intake_byattorney" ("id") DEFERRABLE INITIALLY DEFERRED;
--
-- Alter field id on client
--
ALTER TABLE "intake_client" ALTER COLUMN "id" TYPE integer USING "id"::integer;
DROP SEQUENCE IF EXISTS "intake_client_id_seq" CASCADE;
CREATE SEQUENCE "intake_client_id_seq";
ALTER TABLE "intake_client" ALTER COLUMN "id" SET DEFAULT nextval('"intake_client_id_seq"');
SELECT setval('"intake_client_id_seq"', MAX("id")) FROM "intake_client";
ALTER SEQUENCE "intake_client_id_seq" OWNED BY "intake_client"."id";
--
-- Alter field status on client
--
COMMIT;

Answer 1

I've had exactly the same problem with my project after makemigrations on id as BigAutoField. It turns out that this SQL error is caused by a former issue. If you run a ./manage.py showmigrations it should mention that your 0021 intake migration is not done.

intake
 [X] 0001_initial
 [X] 0002_auto_20220214_1030
 [X] ...
 [ ] intake.0021_auto_20220115_1147

However, django_migrations table in your DB should list this intake.0021 migration. Which means that it is already applied.

SELECT name FROM django_migrations WHERE app = 'intake' AND starts_with(name, '0021');

But, the DB name listed might be slightly different than the existing file name: 0021_auto_20220115_1147 (run @ 11:47). eg in DB: 0021_auto_20220115_1056 (run @ 10:56). This might be due to a makemigrations done on a different server than the one currently used (dev).

Change the file name according to what you have in django_migrations in the DB and run manage.py migrate again and it should work.

The error is caused by the ALTER SEQUENCE statement.

ALTER SEQUENCE "intake_byattorney_id_seq" OWNED BY "intake_byattorney"."id";
ALTER SEQUENCE "intake_client_id_seq" OWNED BY "intake_client"."id";

With postgres, AutoField primary key is managed by a sequence. Why django is not using a ALTER SEQUENCE seq_name as bigint syntax instead of a DROP - CREATE? I don't know but the CREATE SEQUENCE done by manage.py assign a postgres OWNER to the sequence and that's the source of the issue because the table.column to which the sequence is associated has dbuser OWNER.

Therefore the execution of ALTER SEQUENCE OWNED BY statement willing to associate the newly created sequence to the appropriate table.column with a different OWNER triggers the sequence must have same owner as table it is linked to error.