Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#33919 closed Bug (fixed)

Primary keys added in non-initial migration are not created as identity columns in PostgreSQL

Reported by: jackcbrown89 Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 4.1
Severity: Release blocker Keywords: postgres
Cc: Florian Apolloner, Michael Kany Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by jackcbrown89)

In this example, a model was created initially with a primary key on a one-to-one field. The primary key was then moved to a new AutoField. This was working prior to the 4.1 release.

## models.py
class Example(models.Model):
    old_id = models.OneToOneField(
        OtherModel,
        on_delete=models.CASCADE,
        ## primary_key=True,  <--- used to be the primary key
    )

    id = models.AutoField(primary_key=True) # <--- new primary key

Running sqlmigrate on the migration where the primary key changes reveals that the "id" column does not autogenerate values:

$ python manage.py sqlmigrate example 0002

BEGIN;
--
-- Add field id to example
--
ALTER TABLE "example" ADD COLUMN "id" integer NOT NULL PRIMARY KEY;
--
-- Alter field old_id on example
--
ALTER TABLE "example" ALTER COLUMN "old_id" DROP NOT NULL;

When it should be

$ python manage.py sqlmigrate example 0002

BEGIN;
--
-- Add field id to example
--
ALTER TABLE "example" ADD COLUMN "id" integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY;
--
-- Alter field old_id on example
--
ALTER TABLE "example" ALTER COLUMN "old_id" DROP NOT NULL;

This seems to be related to the changes made here: https://code.djangoproject.com/ticket/30511
PR: https://github.com/django/django/pull/15542

Change History (6)

comment:1 by jackcbrown89, 2 years ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 2 years ago

Component: MigrationsDatabase layer (models, ORM)
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

Thanks for the report! Column suffixes (as GENERATED BY...) should be used when adding a new field:

  • django/db/backends/base/schema.py

    diff --git a/django/db/backends/base/schema.py b/django/db/backends/base/schema.py
    index 5e111aed73..f9bf6ed236 100644
    a b class BaseDatabaseSchemaEditor:  
    637637        # It might not actually have a column behind it
    638638        if definition is None:
    639639            return
     640        col_type_suffix = field.db_type_suffix(connection=self.connection)
     641        if col_type_suffix:
     642            definition += f" {col_type_suffix}"
    640643        # Check constraints can go on the column SQL here
    641644        db_params = field.db_parameters(connection=self.connection)
    642645        if db_params["check"]:

Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.

comment:3 by Mariusz Felisiak, 2 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:4 by Mariusz Felisiak, 2 years ago

Has patch: set

comment:5 by GitHub <noreply@…>, 2 years ago

Resolution: fixed
Status: assignedclosed

In 5c803bc:

Fixed #33919 -- Fixed adding AutoFields on PostgreSQL.

Thanks Jack Calvin Brown for the report.

Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

In 3848475e:

[4.1.x] Fixed #33919 -- Fixed adding AutoFields on PostgreSQL.

Thanks Jack Calvin Brown for the report.

Regression in 2eea361eff58dd98c409c5227064b901f41bd0d6.
Backport of 5c803bc0702511c8bc05e9db600367a465514f82 from main

Note: See TracTickets for help on using tickets.
Back to Top