[Django] #24533: Changing an AutoField into an IntegerField leaks the sequence on PostgreSQL
Django
--------------------------------------+------------------------
Reporter: aaugustin | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------+------------------------
'''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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24533>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: aaugustin | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/24533#comment:1>
Django
Reporter: aaugustin | Owner: adambrenecki
Type: Bug | Status: assigned
Component: Migrations | Version: 1.7
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by adambrenecki):
* owner: nobody => adambrenecki
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/24533#comment:2>
Django
Reporter: aaugustin | Owner:
Type: Bug | Status: new
Component: Migrations | Version: 1.7
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by adambrenecki):
* owner: adambrenecki =>
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/24533#comment:3>
Django
Reporter: Aymeric Augustin | Owner: Tim
| Graham
Type: Bug | Status: assigned
Component: Migrations | Version: 1.7
Severity: Normal | Resolution:
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* owner: nobody => Tim Graham
* status: new => assigned
* stage: Accepted => Ready for checkin
Comment:
I added fix for Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/24533#comment:3>
Django
Reporter: Aymeric Augustin | Owner: Tim
| Graham
Component: Migrations | Version: 1.7
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"ea880ec233ecd61c20b74eb7d6d1cf1223897179" ea880ec]:
{{{
#!CommitTicketReference repository=""
revision="ea880ec233ecd61c20b74eb7d6d1cf1223897179"
Fixed #24533 -- Dropped PostgreSQL sequence and Oracle identity when
migrating away from AutoField.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24533#comment:4>