Tag Archives: postgresql

Django/Postgresql – inserting new record doesn’t return id.

You have an old database and you find the following happens.

>>> ap = ValidAirPressureCode(code='9', description='9', barcode='9', comments='
9'
)
>>> ap.save()
>>> ap.id
>>> ap = ValidAirPressureCode.objects.get(code='9')
>>> ap.id
11

In short Django doesn’t return the id of a newly inserted record. If you insert records in admin and choose to continue editing, you’ll get an error saying it can’t find a record with a null id. The problem (in my case) is that Django uses a postgres function called “pg_get_serial_sequence” to identify where the last inserted number given to a table’s record is stored but if your tables were not created using the serial type, this will return null. In my case it was because the database was nine years old and serial types did not exist then.

A proposed solution can be seen on this ticket but at the time of writing it is not implemented.

To fix it in my case I took a look at what my sequences were called. For example:

id | integer| \
    not null default \
      nextval(('t_valid_airpressure_pk_seq'::text)::regclass)

. I then wrote the following piece of middleware (just inserted a call to it in my settings.py middleware section).

class FixLastInsertId(object):

    def __init__(self):
        """ """
        def my_last_insert_id(self, cursor, table_name, pk_name):
            """ This code fails on the alc database, so we fall back to a method of
            getting the last id that does work"""

            sql = "SELECT CURRVAL(pg_get_serial_sequence('%s','%s'))" % (self.quote_name(table_name), pk_name)
            cursor.execute(sql)
            result = cursor.fetchone()[0]
            if not result:
                sql = "select currval('%s_pk_seq'::regclass)" % table_name
                cursor.execute(sql)
                result = cursor.fetchone()[0]
            return result

        from django.db import connection
        connection.ops.__class__.last_insert_id = my_last_insert_id

        # Tell Django to forget about this middleware now, we have
        # had our evil way.
        from django.core.exceptions import MiddlewareNotUsed
        raise MiddlewareNotUsed

Basically I monkey patch the last_insert_id method. I call the normal code and if that fails try with my own way. All my sequences have the same naming convention (tablename_pk_seq) so it works for me.

Thanks to Ross for pointing me in the right direction, although he didn’t approve of my monkey patching solution 😉