id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24533	Changing an AutoField into an IntegerField leaks the sequence on PostgreSQL	Aymeric Augustin	Tim Graham	"'''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."	Bug	closed	Migrations	1.7	Normal	fixed			Ready for checkin	1	0	0	0	0	0