I wrote a web scraping utility in order to import recipes into my personal cookbook/kitchen management application. After a database restore/refresh using Sequelize seeds of previously exported JSON files (using DBeaver) my script had issues with UniqueViolation and primary keys.
In hindsight, and knowing that a data refresh created this issue, it is easy to determine that the sequences are set tostart at 0, rather than the MAX(id) of the primary key column in your tables.
This problem is described in the PostgreSQL Documentation and they provide a very neat solution to it. The following script will generate the script required to reset all your sequences to produce valid primary keys according to the primary key values already in use.
Script Generator Script
SELECT 'SELECT SETVAL(' ||
quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
pg_depend AS D,
pg_class AS T,
pg_attribute AS C,
pg_tables AS PGT
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
AND T.relname = PGT.tablename
ORDER BY S.relname;For me, this resulted in the following output. Using DBeaver, you can select the SQL only as it default to text column selection mode making working with Databases much easier.
Output (snippet)
?column? |
--------------------------------------------------------------------------------------------------------|
SELECT SETVAL('public.barcode_id_seq', COALESCE(MAX(id), 1) ) FROM public.barcode; |
SELECT SETVAL('public.chore_history_id_seq', COALESCE(MAX(id), 1) ) FROM public.chore_history; |
SELECT SETVAL('public.chores_id_seq', COALESCE(MAX(id), 1) ) FROM public.chores; |
SELECT SETVAL('public.ingredients_id_seq', COALESCE(MAX(id), 1) ) FROM public.ingredients; |Executing those commands will fix up all your sequences. The full logging output of the error trace is included below.
Full logging output for SEO
{'raw': '750g stewing steak, such as chuck, cut into strips'}
{'name': 'steak'}
Traceback (most recent call last):
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "units_pkey"
DETAIL: Key (id)=(3) already exists.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File ".\scrape.py", line 69, in
unprocessableIngredients = process_ingredients(session,recipe, scraper.ingredients())
File "C:\Users\danie\Documents\repos\recipe_scraper\common.py", line 136, in process_ingredients
ingredient = get_or_create(session=session, model=Ingredient, defaults={'raw': i}, name=ii['name'].lower())
File "C:\Users\danie\Documents\repos\recipe_scraper\common.py", line 37, in get_or_create
instance = session.query(model).filter_by(**kwargs).first()
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\query.py", line 3228, in first
ret = list(self[0:1])
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\query.py", line 3018, in __getitem__
return list(res)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\query.py", line 3329, in __iter__
self.session._autoflush()
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 1588, in _autoflush
util.raise_from_cause(e)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
raise value
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 1577, in _autoflush
self.flush()
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 2459, in flush
self._flush(objects)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 2597, in _flush
transaction.rollback(_capture_exception=True)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
raise value
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\session.py", line 2557, in _flush
flush_context.execute()
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 422, in execute
rec.execute(self)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 589, in execute
uow,
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\persistence.py", line 245, in save_obj
insert,
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\orm\persistence.py", line 1138, in _emit_insert_statements
statement, params
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 988, in execute
return meth(self, multiparams, params)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1107, in _execute_clauseelement
distilled_params,
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
raise value.with_traceback(tb)
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "C:\Users\danie\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "units_pkey"
DETAIL: Key (id)=(3) already exists.
[SQL: INSERT INTO units (name, plural, "otherNames") VALUES (%(name)s, %(plural)s, %(otherNames)s) RETURNING units.id]
[parameters: {'name': 'stewing', 'plural': None, 'otherNames': None}]
(Background on this error at: http://sqlalche.me/e/gkpj)
Happy days.