autogenerated 'id' field from Django model doesn't autoincrement?

[Ken MacDonald]

I have a postgreSQL / psycopg2 DB generated by my Django model with a single user table (normally read-only) that will be updated via batch job about once a week. The PK is the autogenerated 'id' field; however in postgreSQL the generated field is of type 'integer' rather than type 'serial' which is the postgreSQL auto-incrementing type. So, in my batch update job (no Django involved here) I tried to:

INSERT INTO tablename (a, b, c) VALUES ('aa', 'bb', 'cc')

but it complained about a 'duplicate PK error' on id. I found that the sequence 'tablename_id_seq' was not being updated, since it's not a 'serial' field.

What I ended up doing:

First time through the update/insert loop, set the auto-increment sequence counter:
SELECT setval('tablename_id_seq', (SELECT max(id) from tablename))

which ensures that the sequence starts at the proper spot;

then for each INSERT:
INSERT INTO tablename (id, a, b, c) VALUES (nextval('tablename_id_seq'), 'aa', 'bb', 'cc')

So, a couple questions:

1) is it a bug that 'id' is generated as an 'integer' rather than 'serial' type?

2) Is there a way to get Django's model to generate a 'serial' 'id' column that is designated as the PK, so I don't have to explicit specify the 'id' value in my INSERT?

3) Failing that, is there a better way to do the raw SQL steps outlined above?

Thanks,
Ken

[James Bennett]

On Thu, Nov 19, 2009 at 1:57 PM, Ken MacDonald <drke...@gmail.com> wrote:
> So, a couple questions:
>
> 1) is it a bug that 'id' is generated as an 'integer' rather than 'serial'
> type?

I'd be curious as to how your DB got set up that way, since Django's
table-creation routines map AutoField to SERIAL:

http://code.djangoproject.com/browser/django/trunk/django/db/backends/postgresql/creation.py#L10

> 3) Failing that, is there a better way to do the raw SQL steps outlined
> above?

http://docs.djangoproject.com/en/dev/ref/django-admin/#sqlsequencereset-appname-appname


--
"Bureaucrat Conrad, you are technically correct -- the best kind of correct."

[Ken MacDonald]

> 1) is it a bug that 'id' is generated as an 'integer' rather than 'serial'
> type?

I'd be curious as to how your DB got set up that way, since Django's
table-creation routines map AutoField to SERIAL:

http://code.djangoproject.com/browser/django/trunk/django/db/backends/postgresql/creation.py#L10

Define a models.py as:

from django.db import models

# Create your models here.
class Cardrange(models.Model):
    minbin = models.DecimalField(max_digits=12, decimal_places=0)
    maxbin = models.DecimalField(max_digits=12, decimal_places=0)
   
    minpan = models.IntegerField()
    maxpan = models.IntegerField()

... more fields and 'def __unicode__() ...

then run  'manage.py sysncdb'

Just dropped my table and re-created it, and the 'id' autogenerated field is an integer as shown by pgadmin III.
 

> 3) Failing that, is there a better way to do the raw SQL steps outlined
> above?

http://docs.djangoproject.com/en/dev/ref/django-admin/#sqlsequencereset-appname-appname

Thanks, that's a bit nicer!
Ken

[Karen Tracey]

On Thu, Nov 19, 2009 at 4:50 PM, Ken MacDonald <drke...@gmail.com> wrote:

> 1) is it a bug that 'id' is generated as an 'integer' rather than 'serial'
> type?

I'd be curious as to how your DB got set up that way, since Django's
table-creation routines map AutoField to SERIAL:

http://code.djangoproject.com/browser/django/trunk/django/db/backends/postgresql/creation.py#L10

Define a models.py as:

from django.db import models

# Create your models here.
class Cardrange(models.Model):
    minbin = models.DecimalField(max_digits=12, decimal_places=0)
    maxbin = models.DecimalField(max_digits=12, decimal_places=0)
   
    minpan = models.IntegerField()
    maxpan = models.IntegerField()

... more fields and 'def __unicode__() ...

then run  'manage.py sysncdb'

Just dropped my table and re-created it, and the 'id' autogenerated field is an integer as shown by pgadmin III.
 

I cannot recreate this.  Cut-and-paste of your model into a new app, set:

DATABASE_ENGINE = 'postgresql_psycopg2'

in settings.py, add the app to INSTALLED_APPS, run syncdb, and  pgadmin III report the autogenerated id field is 'id serial NOT NULL'.  manage.py sql on the app reports:

BEGIN;
CREATE TABLE "ttt_cardrange" (
    "id" serial NOT NULL PRIMARY KEY,
    "minbin" numeric(12, 0) NOT NULL,
    "maxbin" numeric(12, 0) NOT NULL,
    "minpan" integer NOT NULL,
    "maxpan" integer NOT NULL
)
;
COMMIT;

In your case, what does manage.py sql for the app report?

Karen

[Karen Tracey]

On Thu, Nov 19, 2009 at 4:50 PM, Ken MacDonald <drke...@gmail.com> wrote:

Define a models.py as:

from django.db import models

# Create your models here.
class Cardrange(models.Model):
    minbin = models.DecimalField(max_digits=12, decimal_places=0)
    maxbin = models.DecimalField(max_digits=12, decimal_places=0)
   
    minpan = models.IntegerField()
    maxpan = models.IntegerField()

... more fields and 'def __unicode__() ...

Are you absolutely sure there is not:

    id = models.IntegerField(primary_key=True)

among the "more fields"?

Karen
 

[Ken MacDonald]

from django.db import models

# Create your models here.
class Cardrange(models.Model):
    minbin = models.DecimalField(max_digits=12, decimal_places=0)
    maxbin = models.DecimalField(max_digits=12, decimal_places=0)
   
    minpan = models.IntegerField()
    maxpan = models.IntegerField()

... more fields and 'def __unicode__() ...

Are you absolutely sure there is not:

    id = models.IntegerField(primary_key=True)

among the "more fields"

Karen

Hi,
There's no 'id' in my other fields; the manage.py sql DOES report it as being 'serial', however the field definition in pgadmin III reports:

-- Column: id

-- ALTER TABLE cardtype_cardrange DROP COLUMN id;

ALTER TABLE cardtype_cardrange ADD COLUMN id integer;
ALTER TABLE cardtype_cardrange ALTER COLUMN id SET STORAGE PLAIN;
ALTER TABLE cardtype_cardrange ALTER COLUMN id SET NOT NULL;
ALTER TABLE cardtype_cardrange ALTER COLUMN id SET DEFAULT nextval('cardtype_cardrange_id_seq'::regclass);

but the table creation code has it as serial:

CREATE TABLE tablename
(
  id serial NOT NULL,
  minbin numeric(12) NOT NULL,
  maxbin numeric(12) NOT NULL,
  minpan integer NOT NULL,
  maxpan integer NOT NULL,

The actual characteristics of the field from pgadmin ||| include:
Name: id
Data type: integer
Default: nextval(<the sequence's name >)
Sequence: <the sequence name - which is tablename_id_seq >
Primary key yes
not NULL yes
...

So, it has a sequence, but the sequence is out-of-sync (value=7) with the actual DB (max id is 92,000+), thus the code to first setval() the sequence. Anyway the code seems to work, but it's still odd that the type isn't serial.
Ken

[Karen Tracey]

All of this looks correct.  Note the data type of integer is not wrong -- serial is not a true data type.  See:

http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL

 

 

So, it has a sequence, but the sequence is out-of-sync (value=7) with the actual DB (max id is 92,000+), thus the code to first setval() the sequence. Anyway the code seems to work, but it's still odd that the type isn't serial.

I think the question is not why was the wrong type created (it wasn't) but how did the sequence get out of sync with the actual values in the table?

Karen

[Ken MacDonald]

All of this looks correct.  Note the data type of integer is not wrong -- serial is not a true data type.  See:

http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL

 

 

So, it has a sequence, but the sequence is out-of-sync (value=7) with the actual DB (max id is 92,000+), thus the code to first setval() the sequence. Anyway the code seems to work, but it's still odd that the type isn't serial.

I think the question is not why was the wrong type created (it wasn't) but how did the sequence get out of sync with the actual values in the table?

Karen

Ah. This begins to make sense, now. I think that the sequence must  not have been set when I did the initial load of the data, as the original data had its own sequence numbers and did not use 'nextval' to generate them. I've changed the initial data loader to use nextval instead of the data's sequence numbers, so shouldn't have this problem again. I'm leaving the setval() in my code (modified as in a prev. reply) so that the sequence will reset to 1 if the table rows are all dropped.
Ken