Changing an AutoField into an IntegerField leaks the sequence on PostgreSQL

[Aymeric Augustin]

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.

[Tim Graham]

​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.

[Mariusz Felisiak]

I added fix for Oracle.

[Mariusz Felisiak <felisiak.mariusz@…>]

In ea880ec:

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