Issues importing fixtures with id=0 and postgres backend?

Using Django
1

Hey,
Getting a bit of a corner case and wondering what you guys think.

So I’m importing fixtures, say this test.json:

[
    {
        "model": "admin_account.terme",
        "pk": 0,
        "fields":
        {
            "desc_fr": "Net 30 jours",
            "desc_en": "30 days net",
        }
    }
]

with model

class Terme(models.Model):
    desc_fr = models.CharField(max_length=25, verbose_name='Desc_fr', blank=True, null=True)
    desc_en = models.CharField(max_length=25, verbose_name='Desc_en', blank=True, null=True)

If I import it as-is, I get:

Traceback (most recent call last):
  File "/home/fv/Documents/pro-2020/voltec/VOLTEC-PROJECT/VOLTEC-WEBAPP/app/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.NumericValueOutOfRange: setval: value 0 is out of bounds for sequence "admin_account_terme_id_seq" (1..2147483647)

However, if I simply add another fixture, even in the same import, with id=0, then the importation works just fine.

I know from the doc that the default id field is AutoField, and at least SmallAutofield and BigAutoField mention values from 1…XXXXX.

The data I’m importing does contain a few id=0, and in the db they typically refer to unknown/missing/placeholder stuff. I am not 100% certain that I can just re-assign another id to that field, as perhaps other services expect this behavior.

So… am I likely fine to import id=0? Or should I take the issue to mean I need to find another way to do things?

2

Conjecture / question - Is this only happening on the first import?

In other words, if you empty the table and do not delete it, and then import the single value, does it work? (I have a hypothesis about this dealing with the status of a freshly-created sequence object.)

Also, if you import multiple entries all with the same PK=0, do you get multiple entries in the table, or just one?

Finally, don’t forget that you can use the -v 3 parameter on loaddata to get more details logged.

3

If I empty the table (without dropping it) & try to re-import, I still get the same error.

If I try to import id=0 alone, but in an already-populated table, it imports fine and shows up in the db.

If I import an id=0 AND an id=1, THEN I delete the id=1 entry, and try to import id=0 again, I still get the same error.

The additional logs didn’t turn up additional details.

To me it SEEMS like this is sort of a pg (or a pg db wrapper perhaps) issue, fairly confident I did the same thing with a sqlite3 backend earlier and didn’t have any issues

4

That error looks like it’s directly from PostgreSQL. I know that django auto fields create primary key sequences in the database. You can confirm in a psql shell with \d <table_name>. Looking at the CREATE SEQUENCE docs for postgres shows there is a minimum value option. I do not recall if Django will specify a custom minimum value. So assuming that it doesn’t, the docs indicate that the minimum value would be 1.

If you need to manipulate that sequence’s minimum value, you’re going to have to write a custom sql migration to modify that database instance.

Personal opinion
If you can easily set that primary key to 1 rather than 0, I would go that route. I don’t like managing DB manually like that unless it’s absolutely necessary, really easy, or is very low risk.

5

I’m finding it interesting that this also works for me if there is already at least one row of data in that table. If the table is empty, I get that error. If I insert one row into the table, the loaddata completes without error - even if there’s only one record in the json file.

Also, if I have multiple records in the JSON file all with a pk of 0, only the last one is retained.

6

That is odd. I don’t have the ability to reproduce this quickly, but if you have an empty table and set the pk to 5, does it insert with a pk of 5? And what does that do to the sequence in the database? Is that set to 1 or 6?

Similarly, if there are elements in the table and you load an instance with a pk that’s greater than the next sequence value, what does that do to the sequence?

7

It sets the sequence to that last value inserted.

Subsequent “pk-less” records are inserted after that.

Now, if you go back and insert a lower pk, it does not reset the sequence backward - and now I think I realize that’s likely to be the key to this issue.

With an empty table, inserting a row with pk=0 is going to try and set the last_value of the sequence to 0 - creating that error.

If there’s already data in the table, the last_value is already greater than 0 and an insert of pk=0 is not going to try and reset it to an invalid value.

Also, if you’re trying to insert multiple rows, it appears that all the rows are being inserted as one transaction. The last_value is going to be set to the highest value, also preventing an error.

I think we’ve solved the mystery!

1 Like
8

Thanks for doing that leg work!

9

Right - it doesn’t care per se about the pk/id value in the table… but it will not set the last_value to 0. So the auto-increment mechanism is the root cause.

Interesting - but then I’m going to think it’s probably fine having those id=0 there, just going to check that they fetch properly (they did with sqlite3) and I can edit the record.

So what… is that a minor bug? Should open a ticket on it or not?

10

It’s not a bug - definitely not in Django. If anything, it’s an application issue. I would no more expect Django to accept that than I would expect it to accept a negative value, a non-integer, or a non-numeric.

Quoting directly from the docs:

A 64-bit integer, much like an AutoField except that it is guaranteed to fit numbers from 1 to 9223372036854775807 .

So zero is not defined as a valid value. (Note also how it’s worded. It’s not saying that those are the only valid values, only that that is the range that is guaranteed to work. The results from using any other value is undefined.)

Also, what you need to verify is whether or not you’re going to get all those “id=0” rows inserted. It did not work that way in my testing. Only the last row was in the table.

1 Like