I am trying to move the recorder integration over to PostgeSQL on a different server. I can get a connection but all sesor entities throw this error/exception. On a restart, it is not always the same sensor, but usually a group of 3 or 4 sensors that have the error. I have seen almost every sonsor do so. If I exclude them, then a different set of sensors error. If I exclude the sensor domain, then it is fine.
Is it a bug?
I’m still on 0.118.4 - An older Raspbian Supervised Install (only out of scope things are SSHD and no apparmour). The install of PostgreSQL is on a separate PVE LXC and sees to be working fine.
2020-12-20 18:03:30 ERROR (Recorder) [homeassistant.components.recorder] Error executing query: (psycopg2.errors.ForeignKeyViolation) insert or update on table "states" violates foreign key constraint "states_old_state_id_fkey"
DETAIL: Key (old_state_id)=(12846) is not present in table "states".
[SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%(domain)s, %(entity_id)s, %(state)s, %(attributes)s, %(event_id)s, %(last_changed)s, %(last_updated)s, %(created)s, %(old_state_id)s) RETURNING states.state_id]
[parameters: {'domain': 'switch', 'entity_id': 'switch.sonoffpow1', 'state': 'on', 'attributes': '{"friendly_name": "SonoffPow1"}', 'event_id': 24744, 'last_changed': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'last_updated': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'created': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'old_state_id': 12846}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2020-12-20 18:03:30 ERROR (Recorder) [homeassistant.components.recorder] Error saving events: (psycopg2.errors.ForeignKeyViolation) insert or update on table "states" violates foreign key constraint "states_old_state_id_fkey"
DETAIL: Key (old_state_id)=(12846) is not present in table "states".
[SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%(domain)s, %(entity_id)s, %(state)s, %(attributes)s, %(event_id)s, %(last_changed)s, %(last_updated)s, %(created)s, %(old_state_id)s) RETURNING states.state_id]
[parameters: {'domain': 'switch', 'entity_id': 'switch.sonoffpow1', 'state': 'on', 'attributes': '{"friendly_name": "SonoffPow1"}', 'event_id': 24744, 'last_changed': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'last_updated': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'created': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'old_state_id': 12846}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.ForeignKeyViolation: insert or update on table "states" violates foreign key constraint "states_old_state_id_fkey"
DETAIL: Key (old_state_id)=(12846) is not present in table "states".
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 454, in _commit_event_session_or_retry
self._commit_event_session()
File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 509, in _commit_event_session
self.event_session.flush()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2536, in flush
self._flush(objects)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2678, in _flush
transaction.rollback(_capture_exception=True)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2638, in _flush
flush_context.execute()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 419, in execute
n.execute_aggregate(self, set_)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 697, in execute_aggregate
persistence.save_obj(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
_emit_insert_statements(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
result = cached_connections[connection].execute(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "states" violates foreign key constraint "states_old_state_id_fkey"
DETAIL: Key (old_state_id)=(12846) is not present in table "states".
[SQL: INSERT INTO states (domain, entity_id, state, attributes, event_id, last_changed, last_updated, created, old_state_id) VALUES (%(domain)s, %(entity_id)s, %(state)s, %(attributes)s, %(event_id)s, %(last_changed)s, %(last_updated)s, %(created)s, %(old_state_id)s) RETURNING states.state_id]
[parameters: {'domain': 'switch', 'entity_id': 'switch.sonoffpow1', 'state': 'on', 'attributes': '{"friendly_name": "SonoffPow1"}', 'event_id': 24744, 'last_changed': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'last_updated': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'created': datetime.datetime(2020, 12, 20, 18, 3, 29, 160592, tzinfo=datetime.timezone.utc), 'old_state_id': 12846}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)