search

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
73.45k stars 30.69k forks source link

Recorder states_state_id_seq overflow (#119167) can still occur with Postgres #129694

Open raineth opened 4 days ago

raineth commented 4 days ago

The problem

In issue #119167/PR #121025, the size of IDs was increased to a BIGINT. Unfortunately, it appears that the fix did not alter existing sequences, as I'm still receiving sequence errors on 2024.10.x with BIGINT id columns: psycopg2.errors.SequenceGeneratorLimitExceeded: nextval: reached maximum value of sequence "states_state_id_seq" (2147483647)

I have post-#121025 ID columns:

homeassistant=# \d+ states
                                               Table "public.states"
        Column         |           Type           | Collation | Nullable |                 Default                  | Storage  | Compression | Stats target | Description 
-----------------------+--------------------------+-----------+----------+------------------------------------------+----------+-------------+--------------+-------------
 state_id              | bigint                   |           | not null | nextval('states_state_id_seq'::regclass) | plain    |             |              | 
 domain                | character varying(64)    |           |          |                                          | extended |             |              | 
...etc...

homeassistant=# SELECT * FROM schema_changes ORDER BY changed DESC LIMIT 1;
 change_id | schema_version |            changed            
-----------+----------------+-------------------------------
        41 |             47 | 2024-09-05 00:59:31.433763-04

Manually executing ALTER SEQUENCE states_state_id_seq AS bigint; repaired things for me. I didn't grab the sequence status before fixing it, so contrast with statistics_id_seq:

homeassistant=# \d states_state_id_seq
                    Sequence "public.states_state_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.states.state_id

homeassistant=# \d statistics_id_seq
                 Sequence "public.statistics_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.statistics.id

What version of Home Assistant Core has the issue?

core-2024.10.2

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant Core

Integration causing the issue

recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Diagnostics information

No response

Example YAML snippet

recorder:
  db_url: postgresql://...

Anything in the logs that might be useful for us?

2024-11-02 02:14:40.026 ERROR (Recorder) [homeassistant.components.recorder.core] Unhandled database error while processing task CommitTask()           
Traceback (most recent call last):
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    dialect.do_execute(
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.SequenceGeneratorLimitExceeded: nextval: reached maximum value of sequence "states_state_id_seq" (2147483647)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/srv/homeassistant/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 899, in _process_one_task_or_event_or_recover
    task.run(self)
  File "/srv/homeassistant/lib/python3.12/site-packages/homeassistant/components/recorder/tasks.py", line 297, in run
    instance._commit_event_session_or_retry()  # noqa: SLF001
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 1190, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/srv/homeassistant/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 1226, in _commit_event_session
    session.commit()
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1302, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1277, in _prepare_impl
    self.session.flush()
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4341, in flush
    self._flush(objects)
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4476, in _flush
    with util.safe_reraise():
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4437, in _flush
    flush_context.execute()
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 463, in execute
    n.execute_aggregate(self, set_)
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 759, in execute_aggregate
    persistence.save_obj(
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1143, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_insertmany_context(dialect, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2124, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2116, in _exec_insertmany_context
    dialect.do_execute(
  File "/srv/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.SequenceGeneratorLimitExceeded) nextval: reached maximum value of sequence "states_state_id_seq" (2147483647)

[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_changed_ts, last_reported_ts, last_updated, last_updated_ts, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx, context_id_bin, conte ... 65855 characters truncated ... 17, p18, sen_counter) ORDE
[parameters: {'state__0': '0.0', 'metadata_id__0': 161, 'context_id__0': None, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7e3aa3bd0de0>, 'old_state_id__0': None, 'context_parent_id_bin__0': None, 'attributes_id__0': 6831895, 'last_changed__0': None, 'last_reported_ts__0': None, 'event_id__0': None,
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Additional information

PostgreSQL 16.4 Python 3.12.7

home-assistant[bot] commented 4 days ago

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)