IntegrityError

[rgaudin]

See following logs from the hotspot demo

metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4312, in flush
metrics        |     self._flush(objects)
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4447, in _flush
metrics        |     with util.safe_reraise():
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
metrics        |     raise exc_value.with_traceback(exc_tb)
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4408, in _flush
metrics        |     flush_context.execute()
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
metrics        |     rec.execute(self)
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
metrics        |     util.preloaded.orm_persistence.save_obj(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
metrics        |     _emit_insert_statements(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1227, in _emit_insert_statements
metrics        |     result = connection.execute(
metrics        |              ^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
metrics        |     return meth(
metrics        |            ^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection
metrics        |     return connection._execute_clauseelement(
metrics        |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
metrics        |     ret = self._execute_context(
metrics        |           ^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
metrics        |     return self._exec_single_context(
metrics        |            ^^^^^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
metrics        |     self._handle_dbapi_exception(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, in _handle_dbapi_exception
metrics        |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
metrics        |     self.dialect.do_execute(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
metrics        |     cursor.execute(statement, parameters)
metrics        | sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
metrics        | (sqlite3.IntegrityError) UNIQUE constraint failed: indicator_record.indicator_id, indicator_record.period_id, indicator_record.dimension_id
metrics        | [SQL: INSERT INTO indicator_record (indicator_id, value, period_id, dimension_id) VALUES (?, ?, ?, ?)]
metrics        | [parameters: (1004, 42, 1713902400, 1)]
metrics        | (Background on this error at: https://sqlalche.me/e/20/gkpj)
metrics        | [offspot_metrics::2024-04-26 07:40:50,425] INFO:Tick processing started

[benoit74]

Thank you for reporting that

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be now be reviewed manually. Thank you for your contributions.

[rgaudin]

This is also happening on device and filling up the log as every tick (every minute) generates this once it starts (I think after first visit)

metrics        | [offspot_metrics::2025-09-10 10:25:08,835] INFO:Tick processing started
metrics        | [offspot_metrics::2025-09-10 10:26:08,852] INFO:Tick processing started
metrics        | [offspot_metrics::2025-09-10 10:27:08,870] INFO:Tick processing started
metrics        | [offspot_metrics::2025-09-10 10:27:53,899] INFO:Tick processing started
metrics        | [offspot_metrics::2025-09-10 10:27:53,991] INFO:Tick processing started
metrics        | [offspot_metrics::2025-09-10 10:27:54,021] INFO:Tick processing started
metrics        | [offspot_metrics::2025-09-10 10:27:54,025] WARNING:Error log event
metrics        | Traceback (most recent call last):
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
metrics        |     self.dialect.do_execute(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
metrics        |     cursor.execute(statement, parameters)
metrics        | sqlite3.IntegrityError: UNIQUE constraint failed: indicator_record.indicator_id, indicator_record.period_id, indicator_record.dimension_id
metrics        |
metrics        | The above exception was the direct cause of the following exception:
metrics        |
metrics        | Traceback (most recent call last):
metrics        |   File "/usr/local/lib/python3.12/site-packages/offspot_metrics_backend/main.py", line 108, in handle_log_event
metrics        |     self.processor.process_inputs(result=result)
metrics        |   File "/usr/local/lib/python3.12/site-packages/offspot_metrics_backend/db/__init__.py", line 40, in inner
metrics        |     return func(*args, **kwargs)
metrics        |            ^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/offspot_metrics_backend/business/processor.py", line 85, in process_inputs
metrics        |     self._process_tick(now=current_tick.dt, session=session)
metrics        |   File "/usr/local/lib/python3.12/site-packages/offspot_metrics_backend/business/processor.py", line 139, in _process_tick
metrics        |     self.indicator_processor.process_tick(
metrics        |   File "/usr/local/lib/python3.12/site-packages/offspot_metrics_backend/business/indicators/processor.py", line 77, in process_tick
metrics        |     Persister.persist_indicator_records(
metrics        |   File "/usr/local/lib/python3.12/site-packages/offspot_metrics_backend/db/persister.py", line 62, in persist_indicator_records
metrics        |     db_dimension = session.execute(
metrics        |                    ^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
metrics        |     return self._execute_internal(
metrics        |            ^^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2169, in _execute_internal
metrics        |     ) = compile_state_cls.orm_pre_session_exec(
metrics        |         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 551, in orm_pre_session_exec
metrics        |     session._autoflush()
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3021, in _autoflush
metrics        |     raise e.with_traceback(sys.exc_info()[2])
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3010, in _autoflush
metrics        |     self.flush()
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4312, in flush
metrics        |     self._flush(objects)
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4447, in _flush
metrics        |     with util.safe_reraise():
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
metrics        |     raise exc_value.with_traceback(exc_tb)
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4408, in _flush
metrics        |     flush_context.execute()
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
metrics        |     rec.execute(self)
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
metrics        |     util.preloaded.orm_persistence.save_obj(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
metrics        |     _emit_insert_statements(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1227, in _emit_insert_statements
metrics        |     result = connection.execute(
metrics        |              ^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
metrics        |     return meth(
metrics        |            ^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 517, in _execute_on_connection
metrics        |     return connection._execute_clauseelement(
metrics        |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
metrics        |     ret = self._execute_context(
metrics        |           ^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
metrics        |     return self._exec_single_context(
metrics        |            ^^^^^^^^^^^^^^^^^^^^^^^^^^
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
metrics        |     self._handle_dbapi_exception(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2344, in _handle_dbapi_exception
metrics        |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
metrics        |     self.dialect.do_execute(
metrics        |   File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
metrics        |     cursor.execute(statement, parameters)
metrics        | sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
metrics        | (sqlite3.IntegrityError) UNIQUE constraint failed: indicator_record.indicator_id, indicator_record.period_id, indicator_record.dimension_id
metrics        | [SQL: INSERT INTO indicator_record (indicator_id, value, period_id, dimension_id) VALUES (?, ?, ?, ?)]
metrics        | [parameters: (1001, 1, 1757494800, 4)]
metrics        | (Background on this error at: https://sqlalche.me/e/20/gkpj)