New entry tries to use an existing unique id

[Michel Lavoie]

Hi,

I migrated my previous sqlite3 installation to postgresql, following this discussion: https://groups.google.com/forum/#!msg/django-users/VGG8GTAX4l0/tQUW20HcAwAJ

I'm not facing another weird issue: Whenever I try to create a new item (finance.models.Debtor, see https://github.com/miek770/huitcent/blob/master/finance/models.py), django fails and tells me that primary key (1) is taken. Indeed, the next free id is around 3100. The time after that it tries to take id (2), then (3), and so on. The error looks like this:

IntegrityError at /finance/2/add_transaction/

duplicate key value violates unique constraint "finance_debtor_pkey"
DETAIL:  Key (id)=(3) already exists.

Request Method:	POST
Request URL:	https://myserver/finance/2/add_transaction/
Django Version:	2.1.2
Exception Type:	IntegrityError
Exception Value:	duplicate key value violates unique constraint "finance_debtor_pkey" DETAIL:  Key (id)=(3) already exists.
Exception Location:	/srv/http/huitcent/lib/python3.5/site-packages/django/db/backends/utils.py in _execute, line 85
Python Executable:	/srv/http/huitcent/bin/uwsgi
Python Version:	3.5.3
Python Path:	['.',  '',  '/srv/http/huitcent/lib/python35.zip',  '/srv/http/huitcent/lib/python3.5',  '/srv/http/huitcent/lib/python3.5/plat-arm-linux-gnueabihf',  '/srv/http/huitcent/lib/python3.5/lib-dynload',  '/usr/lib/python3.5',  '/usr/lib/python3.5/plat-arm-linux-gnueabihf',  '/srv/http/huitcent/lib/python3.5/site-packages']
Server time:	jeu, 25 Oct 2018 21:00:14 -0400

As I mentioned before, there are indeed existing entries with these ids. My table definition in postgresql (version 10 looks like this:

forum=# \d+ finance_debtor

                                                    Table "public.finance_debtor"

     Column     |  Type   | Collation | Nullable |                  Default                   | Storage | Stats target | Description

----------------+---------+-----------+----------+--------------------------------------------+---------+--------------+-------------

 id             | integer |           | not null | nextval('finance_debtor_id_seq'::regclass) | plain   |              |

 transaction_id | integer |           | not null |                                            | plain   |              |

 user_id        | integer |           | not null |                                            | plain   |              |

Indexes:

    "finance_debtor_pkey" PRIMARY KEY, btree (id)

    "finance_debtor_transaction_id_7098fd56" btree (transaction_id)

    "finance_debtor_user_id_90f84080" btree (user_id)

Foreign-key constraints:

    "finance_debtor_transaction_id_7098fd56_fk_finance_t" FOREIGN KEY (transaction_id) REFERENCES finance_transaction(id) DEFERRABLE INITIALLY DEFERRED   

    "finance_debtor_user_id_90f84080_fk_auth_user_id" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

And here's postgresql's log output:

2018-10-25 21:00:14.052 EDT [13645] forum@forum ERROR:  duplicate key value violates unique constraint "finance_debtor_pkey"

2018-10-25 21:00:14.052 EDT [13645] forum@forum DETAIL:  Key (id)=(3) already exists.

2018-10-25 21:00:14.052 EDT [13645] forum@forum STATEMENT:  INSERT INTO "finance_debtor" ("user_id", "transaction_id") VALUES (10, 3) RETURNING "finance_debtor"."id"

It looks weird... the SQL statement looks just fine, it's not specifying the id but the other fields... It (the table) was created automatically by django, and I transferred the data using CSV files, making sure the column order matched the new database.

I also tried addind the following line to my Debtor model, with no impact:

id = models.AutoField(primary_key=True)

Any idea what could be wrong here? Does this look like an internal issue for postgresql, or an error during the table's initiation by django, or something else?

Thanks,

Michel

[Sanjay Bhangar]

Hi Michel,

So, a little explanation of why you're facing this issue:

When you create an "auto-increment" field in Postgres, Postgres creates something called a "sequence" to keep track of the increments. If you were to `psql` into your database and check the schema of the table, you'll see the `id` field defined with something like:

    id             | integer                  |           | not null | nextval('content_id_seq'::regclass)

What this means is that to assign the `id` to an object, it will call the "nextval" function on the "content_id_seq" sequence, to get the next number in the sequence. In the normal flow of things, this is not a problem. Every time you insert a new record into the table, it increments the sequence by calling the "nextval" operation and populates the id, and the same for the subsequent row insertion and so on.

Now, the problem arises if you imported data "directly" into the database, i.e. INSERTed rows including the id, in which case, the database will never call the "nextval" function internally, and the "sequence" it uses to keep track of the incrementing ids will never have been updated. I.e. if you manually insert 3000 rows with ids, the nextval() operation on the sequence is never called, and hence postgres still thinks the "sequence" is at 1, and therefore tries to assign the id of, say "2" to the next item, and then of course fails since there is already an item that you manually inserted with id 2.

There would be two ways to solve this:

 - 1> When importing the data, leave out the `id` field, and let postgres populate the id field automatically using the auto-increment nextval() function so the sequence stays in sync.

 - 2> The other option is to manually reset the sequence to be at whatever your highest id value is.

The way to manually update the sequence:

For each table where you are facing this problem:

Run `\d` to see the table schema and get the name of the "sequence" ^^ in my example above, the sequence name is "content_id_seq" - it will generally follow the format of <model_name>_id_seq .

Fetch the highest current id with:

SELECT id FROM <table_name> ORDER BY id DESC LIMIT 1;

Reset the sequence to the value of the highest id:

SELECT setval('content_id_seq', 4555, true);  # assuming your sequence is called content_id_seq and the highest id was 4555 .

If doing this individually for each table in the db seems a bit tedious, you will find various scripts and solutions to automate the process a bit more by searching for something like "postgres reset sequence" - this is a fairly common problem and you should find a bunch of material online.

I hope that helps!

-Sanjay

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/ab6877fd-235a-4916-bfca-f1f2c0f0f3e1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Michel Lavoie]

Wow thank you Sanjay, this is beyond helpful! I'll try the steps you suggested; I need the ids to remain the same.

Michel

[Michel Lavoie]

All good now, thanks again. I ended up using this one-liner to do what you described:

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

Found here: https://gist.github.com/henriquemenezes/962829815e8e7875f5f4376133b2f209

Michel