I have come across a problem a number of times that a table no longer works after you put a backup. You can't add any record at all. Your table sequence might be out of sync and that you have to manually bring it back in sync.
duplicate key value violates unique constraint 👀
return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "job_statpersource_pkey"
DETAIL: Key (id)=(7199) already exists.
SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_sequence');
For Capital letters name of sequence you have to use ""
SELECT nextval('"AccountingPeriod_accountingperiod_id_seq"');
If the first value is higher than the second, your sequence is out of sync. Backup your PG database (just in case), then run the following commands:
SELECT MAX(id) FROM job_statpersource;
Find Sequence in database
SELECT sequence_schema, sequence_name FROM information_schema.sequences ORDER BY sequence_name
\ds
job_statpersource_id_seq
SELECT nextval('job_statpersource_id_seq');
vindazo_nl=> SELECT nextval('job_statpersource_id_seq');
nextval
---------
7200
(1 row)
vindazo_nl=> SELECT MAX(id) FROM job_statpersource;
max
------
7245
(1 row)
So it's clear that something went wrong.. We need to sync it..
And then we need a script that will loop through all the tables automatically and check this way. Is that possible?
SQL Sql with which you can restore operation.
SELECT nextval('"AccountingPeriod_accountingperiod_id_seq"');
SELECT setval('job_statpersource_id_seq', (SELECT MAX(id) FROM job_statpersource)+1);
Ok, this is the solution to fix table seq.
Find more problematic sequentions
You can write output from Sql command in a separate txt file.
SELECT sequence_schema, sequence_name FROM information_schema.sequences ORDER BY sequence_name;
Then you can clean this text in VIM.. :%s/ public | //g
You will get lines with seq names
….
auth_user_id_seq
auth_user_user_permissions_id_seq
city2region_city_id_seq
company_company_employees_id_seq
company_company_id_seq
company_nacebel_id_seq
company_sector_id_seq
company_source_id_seq
contact_me_contactme_id_seq
contactme_contact_msg_id_seq
crawler_offlinetext_id_seq
crawler_page_id_seq
crawler_param_id_seq
Crawler_proxytable_id_seq
…
In my case this is a file with 150 names. So you can now read it in python or django command run it in
See here for a django code example.
from django.db import connection
cursor.execute(“SELECT nextval('job_statpersource_id_seq');”)
row = cursor.fetchone()
vim job/management/commands/check_seq_sql.py
from django.conf import settings
from django.db import connection
from django.core.management.base import BaseCommand, CommandError
class Command(BaseCommand):
"""
Send messages from que
"""
def handle(self, *args, **options):
"""
"""
lines = open('/home/admin/deployment/nl_vindazo/nl_vindazo/data/seq.txt', 'r').readlines()
for line in lines:
self.print_counts(line)
def print_counts(self, line):
line = line.strip()
print('------------------- ' + line + '-----------------')
with connection.cursor() as cursor:
sql = "SELECT nextval('" + line + "');"
self.print_sql(sql, cursor)
sql = "SELECT MAX(id) FROM " + line.replace('_id_seq', '') + ";"
self.print_sql(sql, cursor)
print("/////////////////////")
def print_sql(self, sql, cursor):
print(sql)
cursor.execute(sql)
row = cursor.fetchone()
print(row)
With this command you can see then output something like this..
------------------- account_emailaddress_id_seq-----------------
SELECT nextval('account_emailaddress_id_seq');
(187592L,)
SELECT MAX(id) FROM account_emailaddress;
(187589,)
/////////////////////
------------------- account_emailconfirmation_id_seq-----------------
SELECT nextval('account_emailconfirmation_id_seq');
(6L,)
SELECT MAX(id) FROM account_emailconfirmation;
(None,)
/////////////////////
------------------- auth_group_id_seq-----------------
SELECT nextval('auth_group_id_seq');
(6L,)
SELECT MAX(id) FROM auth_group;
(None,)
/////////////////////
------------------- auth_group_permissions_id_seq-----------------
SELECT nextval('auth_group_permissions_id_seq');
(6L,)
SELECT MAX(id) FROM auth_group_permissions;
(None,)
/////////////////////
------------------- auth_permission_id_seq-----------------
SELECT nextval('auth_permission_id_seq');
(364L,)
SELECT MAX(id) FROM auth_permission;
(358,)
/////////////////////
So the next interval must be higher than max id.. If not, you will get an error when creating a key.
Comments
Post a Comment