Recorder Postgresql crashes system

[Wraith2]

The problem

Since updating to 2024.5 and os 12.2 and later the postgres integration that i use to store my homeassistant data has been crashing. The crash causes a restart of various components which makes the system unusable for any sort of interaction.

When loading a dashboard which contains a graph of state data the graph will often get stuck in the "Loading state data" state and will then not show data. After some time an internal exception will occur which takes down the UI disconnecting the browser from the backend.

What version of Home Assistant Core has the issue?

2024.5.3

What was the last working version of Home Assistant Core?

2024.4.4

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Recorder

Link to integration documentation on our website

No response

Diagnostics information

ha log.txt

Example YAML snippet

No response

Anything in the logs that might be useful for us?

eturn self._execute_internal(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
2024-05-11 13:08:25.385 WARNING (Recorder) [homeassistant.util.executor] Thread[DbWorker_2] is still running at shutdown: File "/usr/local/lib/python3.12/threading.py", line 1030, in _bootstrap
    self._bootstrap_inner()
  File "/usr/local/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
    self.run()
  File "/usr/local/lib/python3.12/threading.py", line 1010, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/executor.py", line 18, in _worker_with_shutdown_hook
    _worker(*args, **kwargs)
  File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 92, in _worker
    work_item.run()
  File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/src/homeassistant/homeassistant/components/history/websocket_api.py", line 229, in _generate_historical_response
    history.get_significant_states(
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/__init__.py", line 104, in get_significant_states
    return _target(
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 123, in get_significant_states
    return get_significant_states_with_session(
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 236, in get_significant_states_with_session
    entity_id_to_metadata_id := instance.states_meta_manager.get_many(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/states_meta.py", line 110, in get_many
    for metadata_id, entity_id in execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 231, in execute_stmt_lambda_element
    executed = session.execute(stmt)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
2024-05-11 13:08:25.694 WARNING (Recorder) [homeassistant.util.executor] Thread[DbWorker_0] is still running at shutdown: File "/usr/local/lib/python3.12/threading.py", line 1030, in _bootstrap
    self._bootstrap_inner()
  File "/usr/local/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
    self.run()
  File "/usr/local/lib/python3.12/threading.py", line 1010, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/executor.py", line 18, in _worker_with_shutdown_hook
    _worker(*args, **kwargs)
  File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 92, in _worker
    work_item.run()
  File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/src/homeassistant/homeassistant/components/history/websocket_api.py", line 229, in _generate_historical_response
    history.get_significant_states(
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/__init__.py", line 104, in get_significant_states
    return _target(
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 123, in get_significant_states
    return get_significant_states_with_session(
  File "/usr/src/homeassistant/homeassistant/components/recorder/history/modern.py", line 236, in get_significant_states_with_session
    entity_id_to_metadata_id := instance.states_meta_manager.get_many(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/states_meta.py", line 110, in get_many
    for metadata_id, entity_id in execute_stmt_lambda_element(
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 231, in execute_stmt_lambda_element
    executed = session.execute(stmt)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py", line 603, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)

Additional information

i've been using postgres for a couple of years without problems. version of postgres is 16.3

[home-assistant]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of recorder can trigger bot actions by commenting:

• @home-assistant close Closes the issue.
• @home-assistant rename Awesome new title Renames the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign recorder Removes the current integration label and assignees on the issue, add the integration domain after the command.
• @home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
• @home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

recorder documentation
recorder source
_{^{(message by IssueLinks)}}