Opened 3 years ago

Last modified 10 months ago

#24533 new Bug

Changing an AutoField into an IntegerField leaks the sequence on PostgreSQL

Reported by: Aymeric Augustin Owned by: nobody
Component: Migrations Version: 1.7
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Step 1

Create the following model:

class TestId(models.Model):
    pass

Create migrations:

% ./manage.py makemigrations test_id
Migrations for 'test_id':
  0001_initial.py:
    - Create model TestId
% ./manage.py sqlmigrate test_id 0001
BEGIN;
CREATE TABLE "test_id_testid" ("id" serial NOT NULL PRIMARY KEY);

COMMIT;
% ./manage.py migrate test_id 0001
Operations to perform:
  Target specific migration: 0001_initial, from test_id
Running migrations:
  Applying test_id.0001_initial... OK

Step 2

Change the primary key from an AutoField to an IntegerField:

class TestId(models.Model):
    id = models.IntegerField(primary_key=True)

Create migrations:

% ./manage.py makemigrations test_id
Migrations for 'test_id':
  0002_auto_20150324_2107.py:
    - Alter field id on testid
% ./manage.py sqlmigrate test_id 0002
BEGIN;
ALTER TABLE "test_id_testid" ALTER COLUMN "id" TYPE integer;

COMMIT;
% ./manage.py migrate test_id 0002
Operations to perform:
  Target specific migration: 0002_auto_20150324_2107, from test_id
Running migrations:
  Applying test_id.0002_auto_20150324_2107... OK

At this point the database contains an unused sequence:

--
-- Name: test_id_testid_id_seq; Type: SEQUENCE; Schema: public; Owner: oshop
--

CREATE SEQUENCE test_id_testid_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.test_id_testid_id_seq OWNER TO oshop;

--
-- Name: test_id_testid_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: oshop
--

ALTER SEQUENCE test_id_testid_id_seq OWNED BY test_id_testid.id;

Step 3

Fortunately, if you revert to an AutoField:

class TestId(models.Model):
    pass

and recreate migrations, the sequence is dropped and recreated, avoiding a crash:

% ./manage.py makemigrations test_id
Migrations for 'test_id':
  0003_auto_20150324_2109.py:
    - Alter field id on testid
% ./manage.py sqlmigrate test_id 0003
BEGIN;
ALTER TABLE "test_id_testid" ALTER COLUMN "id" TYPE integer;
DROP SEQUENCE IF EXISTS test_id_testid_id_seq CASCADE;
CREATE SEQUENCE test_id_testid_id_seq;
ALTER TABLE test_id_testid ALTER COLUMN id SET DEFAULT nextval('test_id_testid_id_seq');
SELECT setval('test_id_testid_id_seq', MAX(id)) FROM test_id_testid;

COMMIT;
% ./manage.py migrate test_id 0003
Operations to perform:
  Target specific migration: 0003_auto_20150324_2109, from test_id
Running migrations:
  Applying test_id.0003_auto_20150324_2109... OK

Is it possible to drop the sequence at step 2 rather than step 3?

That way, users will get the same database schema regardless of whether they've squashed migrations. Indeeed, after squashing migrations 1 and 2, the extra sequence isn't created.

Change History (1)

comment:1 Changed 3 years ago by Tim Graham

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