Hi I recently switched from SQLite to Postgres, and HA is so much snappier when fetching large amounts of data :), but I have noticed that I have gaps in my data at regular intervals.
When i look at my logs i can see that it fails to insert rows
2024-04-01 20:14:34.609 ERROR (Recorder) [homeassistant.components.recorder.core] Unhandled database error while processing task CommitTask(): (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "states_meta_pkey"
DETAIL: Key (metadata_id)=(3) already exists.
[SQL: INSERT INTO states_meta (entity_id) SELECT p0::VARCHAR FROM (VALUES (%(entity_id__0)s, 0), (%(entity_id__1)s, 1), (%(entity_id__2)s, 2), (%(entity_id__3)s, 3)) AS imp_sen(p0, sen_counter) ORDER BY sen_counter RETURNING states_meta.metadata_id, states_meta.metadata_id AS metadata_id__1]
[parameters: {'entity_id__0': 'sensor.living_room_right_switch_brightness', 'entity_id__1': 'sensor.living_room_right_switch_action_step_size', 'entity_id__2': 'sensor.living_room_left_switch_brightness', 'entity_id__3': 'sensor.living_room_left_switch_action_step_size'}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2104, in _exec_insertmany_context
dialect.do_execute(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "states_meta_pkey"
DETAIL: Key (metadata_id)=(3) already exists.
Some other errors i have found
(Background on this error at: https://sqlalche.me/e/20/gkpj)
UnboundLocalError: cannot access local variable 'modified_statistic_ids' where it is not associated with a value
Could that be the cause of the gaps?
Also when i inspect the SQLite databas, then there are foreign keys between tables, but those are missing in my postgres database, is that normal?
I’m using the following configuration
recorder:
db_url: !secret postgres
db_retry_wait: 15 # Wait 15 seconds before retrying
I migrated my sqlite database using pgloader to postgres, but i let homeassistant create the database schema.