Ticket #407 (assigned defect)

Opened 4 years ago

Last modified 11 months ago

Can't alter column to AutoField on postgreSQL

Reported by: pjrharley@… Owned by: andrew
Priority: major Milestone: 1.0
Component: migrations Version: 0.7
Keywords: Cc:

Description

After testing my case in ticket #402 I tried it out on postgreSQL and came across the following error:

django.db.utils.DatabaseError?: type "serial" does not exist

I think the problem comes down to the discussion here:

http://old.nabble.com/ALTER-TABLE-with-TYPE-serial-does-not-work-td21769575.html

Change History

comment:1 Changed 4 years ago by andrew

  • Status changed from new to assigned
  • Milestone set to 0.7.1

It's unfortunate that I'll have to special-case this, but I agree, it needs fixing.

comment:2 Changed 4 years ago by andrew

  • Milestone changed from 0.7.1 to 0.7.2

comment:3 Changed 4 years ago by andrew

  • Milestone changed from 0.7.2 to 0.7.3

comment:4 Changed 4 years ago by andrew

  • Milestone changed from 0.7.3 to 1.0

comment:5 Changed 3 years ago by shibz

Any workaround for this?

comment:6 Changed 3 years ago by shibz

I was able to successfully migrate from an integer field to an auto field in postgres using a few custom commands. For anyone else having this problem, you can try this workaround:

  1. Change integer field to auto field in models.
  2. Generate auto schemamigration like usual.
  3. Edit generated file. Under forward/backward delete the alter_table autofield command.
  4. Add the following custom commands in the appropriate function, substituting appropriate column/table names:
def forwards(self, orm):
    db.execute("CREATE SEQUENCE %TABLENAME%_%COLUMNNAME%_seq")
    db.execute("SELECT setval('%TABLENAME%_%COLUMNNAME%_seq', (SELECT MAX(%COLUMNNAME%) FROM %TABLENAME%))")
    db.execute("ALTER TABLE %TABLENAME% ALTER COLUMN %COLUMNNAME% SET DEFAULT nextval('%TABLENAME%_%COLUMNNAME%_seq'::regclass)")


def backwards(self, orm):
    db.execute("ALTER TABLE %TABLENAME% ALTER COLUMN %COLUMNNAME% DROP DEFAULT")
    db.execute("DROP SEQUENCE %TABLENAME%_%COLUMNNAME%_seq")
  1. Run migration

I haven't tested the backwards migration, but I don't see any reason why it wouldn't work.

comment:7 Changed 3 years ago by anonymous

+1 for fixing this and the vaguely related #833, with both fixed you could drop / change primary keys in postgresql

comment:8 Changed 3 years ago by anonymous

I tried the workaround, and now I'm experiencing this problem: http://stackoverflow.com/questions/6412962/django-admin-instance-needs-to-have-a-primary-key-value-before-a-many-to-many

Basically every time I tried to add an object of the type that I did this to in the Admin interface, I get this error:
'Team' instance needs to have a primary key value before a many-to-many relationship can be used.

comment:9 follow-up: ↓ 12 Changed 19 months ago by anonymous

Don't forget in forwards method, to link the Primary Key and the sequence:

db.execute("ALTER SEQUENCE %TABLENAME%_%COLUMNNAME%_seq OWNED BY %TABLENAME%_%COLUMNNAME%.seq")

Else a call to pg_get_serial_sequence postgresql('%TABLENAME%','%COLUMNNAME%') returns NULL, and last_insert_id or sequences resetting at the end of a loaddata do not work (sequence is not updated with last id value).

comment:10 Changed 18 months ago by anonymous

thanks shibz, that worked for me

comment:11 Changed 13 months ago by scottshamus@…

Is there any roadmap to have this fixed ? This issue will affect anyone that uses the default django auto incrementing id and has over 231 objects inserted into the DB. Having a way to easily migrate an int ID to a bigint ID via south will make this problem much cleaner to solve.

The fix documented here: http://stackoverflow.com/questions/2672975/django-biginteger-auto-increment-field-as-primary-key doesn't work as of right now is Postgres. It creates the correct south migration but fails because of this bug.

comment:12 in reply to: ↑ 9 Changed 11 months ago by gbazilio

Replying to anonymous:

Don't forget in forwards method, to link the Primary Key and the sequence:

db.execute("ALTER SEQUENCE %TABLENAME%_%COLUMNNAME%_seq OWNED BY %TABLENAME%_%COLUMNNAME%.seq")

Else a call to pg_get_serial_sequence postgresql('%TABLENAME%','%COLUMNNAME%') returns NULL, and last_insert_id or sequences resetting at the end of a loaddata do not work (sequence is not updated with last id value).

This, combined with shibz's post, has solved my issue. There should be patch to that once this seems to be a commonly used feature.

Note: See TracTickets for help on using tickets.