Opened 9 years ago

Closed 4 years ago

#24533 closed Bug (fixed)

Changing an AutoField into an IntegerField leaks the sequence on PostgreSQL

Reported by: Aymeric Augustin Owned by: Tim Graham
Component: Migrations Version: 1.7
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes 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 (4)

comment:1 by Tim Graham, 9 years ago

Triage Stage: UnreviewedAccepted

comment:2 by Tim Graham, 4 years ago

Has patch: set

PR

The test doesn't pass on Oracle. I'm not sure what the solution looks like there. If the solution isn't straightforward we could skip the new assertion on Oracle and create a separate ticket.

comment:3 by Mariusz Felisiak, 4 years ago

Owner: changed from nobody to Tim Graham
Status: newassigned
Triage Stage: AcceptedReady for checkin

I added fix for Oracle.

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

Resolution: fixed
Status: assignedclosed

In ea880ec:

Fixed #24533 -- Dropped PostgreSQL sequence and Oracle identity when migrating away from AutoField.

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