Integer out of range error when using Postgres as database

[oleks85]

The problem

Hello

I have pretty big amount of data in my HA.
Recently I start noticing missing values on graphs.

In HA logs I see many errors like this "Unhandled database error while processing task CommitTask()" then in database logs I noticed this message: ERROR: integer out of range
and SQL query which is causing issue, please have a look full SQL query in "Anything in the logs that might be useful?"

Then I noticed wrong column type in SQL statement when HA inserting values into database instead of BIGINT int using INTEGER as a type for old_state_id column which is p9 in logs.

What version of Home Assistant Core has the issue?

2024.4.3

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant Container

Integration causing the issue

Recorder

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

In HA error log:
Логгер: homeassistant.components.recorder.core
Джерело: components/recorder/core.py:920
інтеграції: Recorder (документація, проблеми)
Вперше відбулося: 09:36:13 (4333 випадки)
Востаннє записано: 15:38:57

Unhandled database error while processing task CommitTask(): (psycopg2.errors.NumericValueOutOfRange) integer out of range [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 ... 13878 characters truncated ... 17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1] [parameters: {'context_parent_id_bin__0': None, 'attributes_id__0': 842563, 'last_changed__0': None, 'context_parent_id__0': None, 'metadata_id__0': 18, 'last_updated_ts__0': 1713443913.464417, 'context_user_id_bin__0': None, 'origin_idx__0': 0, 'state__0': '232.1', 'last_reported_ts__0': 1713443913.464417, 'old_state_id__0': 2153570249, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7a20d99c7fc0>, 'attributes__0': None, 'context_id__0': None, 'last_changed_ts__0': None, 'context_user_id__0': None, 'entity_id__0': None, 'last_updated__0': None, 'event_id__0': None, 'context_parent_id_bin__1': None, 'attributes_id__1': 842564, 'last_changed__1': None, 'context_parent_id__1': None, 'metadata_id__1': 4, 'last_updated_ts__1': 1713443913.465135, 'context_user_id_bin__1': None, 'origin_idx__1': 0, 'state__1': '49.9', 'last_reported_ts__1': 1713443913.465135, 'old_state_id__1': 2153570250, 'context_id_bin__1': <psycopg2.extensions.Binary object at 0x7a20d99c5cb0>, 'attributes__1': None, 'context_id__1': None, 'last_changed_ts__1': None, 'context_user_id__1': None, 'entity_id__1': None, 'last_updated__1': None, 'event_id__1': None, 'context_parent_id_bin__2': None, 'attributes_id__2': 842565, 'last_changed__2': None, 'context_parent_id__2': None, 'metadata_id__2': 30, 'last_updated_ts__2': 1713443913.465592, 'context_user_id_bin__2': None, 'origin_idx__2': 0, 'state__2': '232.1', 'last_reported_ts__2': 1713443913.465592, 'old_state_id__2': 2153570251, 'context_id_bin__2': <psycopg2.extensions.Binary object at 0x7a20d99c5920> ... 489 parameters truncated ... 'origin_idx__28': 0, 'state__28': 'on', 'last_reported_ts__28': 1713443913.479327, 'old_state_id__28': 2153570277, 'context_id_bin__28': <psycopg2.extensions.Binary object at 0x7a20d7b3eac0>, 'attributes__28': None, 'context_id__28': None, 'last_changed_ts__28': None, 'context_user_id__28': None, 'entity_id__28': None, 'last_updated__28': None, 'event_id__28': None, 'context_parent_id_bin__29': None, 'attributes_id__29': 1198587, 'last_changed__29': None, 'context_parent_id__29': None, 'metadata_id__29': 98, 'last_updated_ts__29': 1713443913.480022, 'context_user_id_bin__29': None, 'origin_idx__29': 0, 'state__29': 'off', 'last_reported_ts__29': 1713443913.480022, 'old_state_id__29': 2153570278, 'context_id_bin__29': <psycopg2.extensions.Binary object at 0x7a20d7b3c4b0>, 'attributes__29': None, 'context_id__29': None, 'last_changed_ts__29': None, 'context_user_id__29': None, 'entity_id__29': None, 'last_updated__29': None, 'event_id__29': None, 'context_parent_id_bin__30': None, 'attributes_id__30': 1093276, 'last_changed__30': None, 'context_parent_id__30': None, 'metadata_id__30': 55, 'last_updated_ts__30': 1713443914.023319, 'context_user_id_bin__30': None, 'origin_idx__30': 0, 'state__30': '0.63', 'last_reported_ts__30': 1713443914.023319, 'old_state_id__30': 2153570283, 'context_id_bin__30': <psycopg2.extensions.Binary object at 0x7a20d7b3db30>, 'attributes__30': None, 'context_id__30': None, 'last_changed_ts__30': None, 'context_user_id__30': None, 'entity_id__30': None, 'last_updated__30': None, 'event_id__30': None}] (Background on this error at: https://sqlalche.me/e/20/9h9h)
Unhandled database error while processing task CommitTask(): (psycopg2.errors.NumericValueOutOfRange) integer out of range [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 ... 13878 characters truncated ... 17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1] [parameters: {'context_parent_id_bin__0': None, 'attributes_id__0': 842563, 'last_changed__0': None, 'context_parent_id__0': None, 'metadata_id__0': 18, 'last_updated_ts__0': 1713443918.457926, 'context_user_id_bin__0': None, 'origin_idx__0': 0, 'state__0': '230.9', 'last_reported_ts__0': 1713443918.457926, 'old_state_id__0': 2153570288, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7a20dbbe89c0>, 'attributes__0': None, 'context_id__0': None, 'last_changed_ts__0': None, 'context_user_id__0': None, 'entity_id__0': None, 'last_updated__0': None, 'event_id__0': None, 'context_parent_id_bin__1': None, 'attributes_id__1': 842564, 'last_changed__1': None, 'context_parent_id__1': None, 'metadata_id__1': 4, 'last_updated_ts__1': 1713443918.458697, 'context_user_id_bin__1': None, 'origin_idx__1': 0, 'state__1': '49.9', 'last_reported_ts__1': 1713443918.458697, 'old_state_id__1': 2153570289, 'context_id_bin__1': <psycopg2.extensions.Binary object at 0x7a20dc8cb4b0>, 'attributes__1': None, 'context_id__1': None, 'last_changed_ts__1': None, 'context_user_id__1': None, 'entity_id__1': None, 'last_updated__1': None, 'event_id__1': None, 'context_parent_id_bin__2': None, 'attributes_id__2': 842565, 'last_changed__2': None, 'context_parent_id__2': None, 'metadata_id__2': 30, 'last_updated_ts__2': 1713443918.459238, 'context_user_id_bin__2': None, 'origin_idx__2': 0, 'state__2': '230.9', 'last_reported_ts__2': 1713443918.459238, 'old_state_id__2': 2153570290, 'context_id_bin__2': <psycopg2.extensions.Binary object at 0x7a20d95491d0> ... 489 parameters truncated ... 'origin_idx__28': 0, 'state__28': 'on', 'last_reported_ts__28': 1713443918.473646, 'old_state_id__28': 2153570316, 'context_id_bin__28': <psycopg2.extensions.Binary object at 0x7a20dd281140>, 'attributes__28': None, 'context_id__28': None, 'last_changed_ts__28': None, 'context_user_id__28': None, 'entity_id__28': None, 'last_updated__28': None, 'event_id__28': None, 'context_parent_id_bin__29': None, 'attributes_id__29': 1198587, 'last_changed__29': None, 'context_parent_id__29': None, 'metadata_id__29': 98, 'last_updated_ts__29': 1713443918.474188, 'context_user_id_bin__29': None, 'origin_idx__29': 0, 'state__29': 'off', 'last_reported_ts__29': 1713443918.474188, 'old_state_id__29': 2153570317, 'context_id_bin__29': <psycopg2.extensions.Binary object at 0x7a20d7aa0330>, 'attributes__29': None, 'context_id__29': None, 'last_changed_ts__29': None, 'context_user_id__29': None, 'entity_id__29': None, 'last_updated__29': None, 'event_id__29': None, 'context_parent_id_bin__30': None, 'attributes_id__30': 1093276, 'last_changed__30': None, 'context_parent_id__30': None, 'metadata_id__30': 55, 'last_updated_ts__30': 1713443921.237952, 'context_user_id_bin__30': None, 'origin_idx__30': 0, 'state__30': '0.64', 'last_reported_ts__30': None, 'old_state_id__30': 2153570318, 'context_id_bin__30': <psycopg2.extensions.Binary object at 0x7a20d7aa2100>, 'attributes__30': None, 'context_id__30': None, 'last_changed_ts__30': None, 'context_user_id__30': None, 'entity_id__30': None, 'last_updated__30': None, 'event_id__30': None}] (Background on this error at: https://sqlalche.me/e/20/9h9h)
Unhandled database error while processing task CommitTask(): (psycopg2.errors.NumericValueOutOfRange) integer out of range [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 ... 13878 characters truncated ... 17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1] [parameters: {'context_parent_id_bin__0': None, 'attributes_id__0': 842563, 'last_changed__0': None, 'context_parent_id__0': None, 'metadata_id__0': 18, 'last_updated_ts__0': 1713443923.453897, 'context_user_id_bin__0': None, 'origin_idx__0': 0, 'state__0': '229.7', 'last_reported_ts__0': 1713443923.453897, 'old_state_id__0': 2153570319, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7a20d83c2910>, 'attributes__0': None, 'context_id__0': None, 'last_changed_ts__0': None, 'context_user_id__0': None, 'entity_id__0': None, 'last_updated__0': None, 'event_id__0': None, 'context_parent_id_bin__1': None, 'attributes_id__1': 842564, 'last_changed__1': None, 'context_parent_id__1': None, 'metadata_id__1': 4, 'last_updated_ts__1': 1713443923.454148, 'context_user_id_bin__1': None, 'origin_idx__1': 0, 'state__1': '49.9', 'last_reported_ts__1': 1713443923.454148, 'old_state_id__1': 2153570320, 'context_id_bin__1': <psycopg2.extensions.Binary object at 0x7a20d83c1ad0>, 'attributes__1': None, 'context_id__1': None, 'last_changed_ts__1': None, 'context_user_id__1': None, 'entity_id__1': None, 'last_updated__1': None, 'event_id__1': None, 'context_parent_id_bin__2': None, 'attributes_id__2': 842565, 'last_changed__2': None, 'context_parent_id__2': None, 'metadata_id__2': 30, 'last_updated_ts__2': 1713443923.45434, 'context_user_id_bin__2': None, 'origin_idx__2': 0, 'state__2': '229.7', 'last_reported_ts__2': 1713443923.45434, 'old_state_id__2': 2153570321, 'context_id_bin__2': <psycopg2.extensions.Binary object at 0x7a20e05bf450> ... 489 parameters truncated ... 'origin_idx__28': 0, 'state__28': 'on', 'last_reported_ts__28': 1713443923.467103, 'old_state_id__28': 2153570347, 'context_id_bin__28': <psycopg2.extensions.Binary object at 0x7a20d7b53480>, 'attributes__28': None, 'context_id__28': None, 'last_changed_ts__28': None, 'context_user_id__28': None, 'entity_id__28': None, 'last_updated__28': None, 'event_id__28': None, 'context_parent_id_bin__29': None, 'attributes_id__29': 1198587, 'last_changed__29': None, 'context_parent_id__29': None, 'metadata_id__29': 98, 'last_updated_ts__29': 1713443923.467582, 'context_user_id_bin__29': None, 'origin_idx__29': 0, 'state__29': 'off', 'last_reported_ts__29': 1713443923.467582, 'old_state_id__29': 2153570348, 'context_id_bin__29': <psycopg2.extensions.Binary object at 0x7a20d7b50f90>, 'attributes__29': None, 'context_id__29': None, 'last_changed_ts__29': None, 'context_user_id__29': None, 'entity_id__29': None, 'last_updated__29': None, 'event_id__29': None, 'context_parent_id_bin__30': None, 'attributes_id__30': 1093276, 'last_changed__30': None, 'context_parent_id__30': None, 'metadata_id__30': 55, 'last_updated_ts__30': 1713443925.122302, 'context_user_id_bin__30': None, 'origin_idx__30': 0, 'state__30': '0.65', 'last_reported_ts__30': None, 'old_state_id__30': 2153570349, 'context_id_bin__30': <psycopg2.extensions.Binary object at 0x7a20d7b50900>, 'attributes__30': None, 'context_id__30': None, 'last_changed_ts__30': None, 'context_user_id__30': None, 'entity_id__30': None, 'last_updated__30': None, 'event_id__30': None}] (Background on this error at: https://sqlalche.me/e/20/9h9h)
Unhandled database error while processing task CommitTask(): (psycopg2.errors.NumericValueOutOfRange) integer out of range [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 ... 14318 characters truncated ... 17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1] [parameters: {'context_parent_id_bin__0': None, 'attributes_id__0': 842563, 'last_changed__0': None, 'context_parent_id__0': None, 'metadata_id__0': 18, 'last_updated_ts__0': 1713443928.448465, 'context_user_id_bin__0': None, 'origin_idx__0': 0, 'state__0': '230.0', 'last_reported_ts__0': 1713443928.448465, 'old_state_id__0': 2153570350, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7a20d7b73870>, 'attributes__0': None, 'context_id__0': None, 'last_changed_ts__0': None, 'context_user_id__0': None, 'entity_id__0': None, 'last_updated__0': None, 'event_id__0': None, 'context_parent_id_bin__1': None, 'attributes_id__1': 842564, 'last_changed__1': None, 'context_parent_id__1': None, 'metadata_id__1': 4, 'last_updated_ts__1': 1713443928.449255, 'context_user_id_bin__1': None, 'origin_idx__1': 0, 'state__1': '49.9', 'last_reported_ts__1': 1713443928.449255, 'old_state_id__1': 2153570351, 'context_id_bin__1': <psycopg2.extensions.Binary object at 0x7a20d7fd3c90>, 'attributes__1': None, 'context_id__1': None, 'last_changed_ts__1': None, 'context_user_id__1': None, 'entity_id__1': None, 'last_updated__1': None, 'event_id__1': None, 'context_parent_id_bin__2': None, 'attributes_id__2': 842565, 'last_changed__2': None, 'context_parent_id__2': None, 'metadata_id__2': 30, 'last_updated_ts__2': 1713443928.449451, 'context_user_id_bin__2': None, 'origin_idx__2': 0, 'state__2': '230.0', 'last_reported_ts__2': 1713443928.449451, 'old_state_id__2': 2153570352, 'context_id_bin__2': <psycopg2.extensions.Binary object at 0x7a20d7fd2df0> ... 508 parameters truncated ... 'origin_idx__29': 0, 'state__29': 'off', 'last_reported_ts__29': 1713443928.463236, 'old_state_id__29': 2153570379, 'context_id_bin__29': <psycopg2.extensions.Binary object at 0x7a20d954b4b0>, 'attributes__29': None, 'context_id__29': None, 'last_changed_ts__29': None, 'context_user_id__29': None, 'entity_id__29': None, 'last_updated__29': None, 'event_id__29': None, 'context_parent_id_bin__30': None, 'attributes_id__30': 1093276, 'last_changed__30': None, 'context_parent_id__30': None, 'metadata_id__30': 55, 'last_updated_ts__30': 1713443929.007976, 'context_user_id_bin__30': None, 'origin_idx__30': 0, 'state__30': '0.65', 'last_reported_ts__30': None, 'old_state_id__30': 2153570380, 'context_id_bin__30': <psycopg2.extensions.Binary object at 0x7a20dcc50600>, 'attributes__30': None, 'context_id__30': None, 'last_changed_ts__30': None, 'context_user_id__30': None, 'entity_id__30': None, 'last_updated__30': None, 'event_id__30': None, 'context_parent_id_bin__31': None, 'attributes_id__31': 3642112, 'last_changed__31': None, 'context_parent_id__31': None, 'metadata_id__31': 1, 'last_updated_ts__31': 1713443929.794177, 'context_user_id_bin__31': None, 'origin_idx__31': 0, 'state__31': 'on', 'last_reported_ts__31': None, 'old_state_id__31': None, 'context_id_bin__31': <psycopg2.extensions.Binary object at 0x7a20dbba9740>, 'attributes__31': None, 'context_id__31': None, 'last_changed_ts__31': 1713422164.92468, 'context_user_id__31': None, 'entity_id__31': None, 'last_updated__31': None, 'event_id__31': None}] (Background on this error at: https://sqlalche.me/e/20/9h9h)
Unhandled database error while processing task CommitTask(): (psycopg2.errors.NumericValueOutOfRange) integer out of range [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 ... 13878 characters truncated ... 17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1] [parameters: {'context_parent_id_bin__0': None, 'attributes_id__0': 842563, 'last_changed__0': None, 'context_parent_id__0': None, 'metadata_id__0': 18, 'last_updated_ts__0': 1713443933.451943, 'context_user_id_bin__0': None, 'origin_idx__0': 0, 'state__0': '230.2', 'last_reported_ts__0': 1713443933.451943, 'old_state_id__0': 2153570390, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7a20d551ebb0>, 'attributes__0': None, 'context_id__0': None, 'last_changed_ts__0': None, 'context_user_id__0': None, 'entity_id__0': None, 'last_updated__0': None, 'event_id__0': None, 'context_parent_id_bin__1': None, 'attributes_id__1': 842564, 'last_changed__1': None, 'context_parent_id__1': None, 'metadata_id__1': 4, 'last_updated_ts__1': 1713443933.452178, 'context_user_id_bin__1': None, 'origin_idx__1': 0, 'state__1': '49.9', 'last_reported_ts__1': 1713443933.452178, 'old_state_id__1': 2153570391, 'context_id_bin__1': <psycopg2.extensions.Binary object at 0x7a20d62258c0>, 'attributes__1': None, 'context_id__1': None, 'last_changed_ts__1': None, 'context_user_id__1': None, 'entity_id__1': None, 'last_updated__1': None, 'event_id__1': None, 'context_parent_id_bin__2': None, 'attributes_id__2': 842565, 'last_changed__2': None, 'context_parent_id__2': None, 'metadata_id__2': 30, 'last_updated_ts__2': 1713443933.452336, 'context_user_id_bin__2': None, 'origin_idx__2': 0, 'state__2': '230.2', 'last_reported_ts__2': 1713443933.452336, 'old_state_id__2': 2153570392, 'context_id_bin__2': <psycopg2.extensions.Binary object at 0x7a20d62272a0> ... 489 parameters truncated ... 'origin_idx__28': 0, 'state__28': 'on', 'last_reported_ts__28': 1713443933.455824, 'old_state_id__28': 2153570419, 'context_id_bin__28': <psycopg2.extensions.Binary object at 0x7a20d8168540>, 'attributes__28': None, 'context_id__28': None, 'last_changed_ts__28': None, 'context_user_id__28': None, 'entity_id__28': None, 'last_updated__28': None, 'event_id__28': None, 'context_parent_id_bin__29': None, 'attributes_id__29': 1198587, 'last_changed__29': None, 'context_parent_id__29': None, 'metadata_id__29': 98, 'last_updated_ts__29': 1713443933.455938, 'context_user_id_bin__29': None, 'origin_idx__29': 0, 'state__29': 'off', 'last_reported_ts__29': 1713443933.455938, 'old_state_id__29': 2153570420, 'context_id_bin__29': <psycopg2.extensions.Binary object at 0x7a20d816a9d0>, 'attributes__29': None, 'context_id__29': None, 'last_changed_ts__29': None, 'context_user_id__29': None, 'entity_id__29': None, 'last_updated__29': None, 'event_id__29': None, 'context_parent_id_bin__30': None, 'attributes_id__30': 1093276, 'last_changed__30': None, 'context_parent_id__30': None, 'metadata_id__30': 55, 'last_updated_ts__30': 1713443936.21765, 'context_user_id_bin__30': None, 'origin_idx__30': 0, 'state__30': '0.67', 'last_reported_ts__30': None, 'old_state_id__30': 2153570399, 'context_id_bin__30': <psycopg2.extensions.Binary object at 0x7a20d8169aa0>, 'attributes__30': None, 'context_id__30': None, 'last_changed_ts__30': None, 'context_user_id__30': None, 'entity_id__30': None, 'last_updated__30': None, 'event_id__30': None}] (Background on this error at: https://sqlalche.me/e/20/9h9h)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2120, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.NumericValueOutOfRange: integer out of range


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

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 920, in _process_one_task_or_event_or_recover
    return task.run(self)
           ^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 292, in run
    instance._commit_event_session_or_retry()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1184, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1219, in _commit_event_session
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1972, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1257, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1232, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4296, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4431, in _flush
    with util.safe_reraise():
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4392, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 463, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 759, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1143, in _emit_insert_statements
    result = connection.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/elements.py", line 514, 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 1848, in _execute_context
    return self._exec_insertmany_context(dialect, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2128, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2120, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.NumericValueOutOfRange) integer out of range

[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 ... 16958 characters truncated ... 17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1]
[parameters: {'context_parent_id_bin__0': None, 'attributes_id__0': 842563, 'last_changed__0': None, 'context_parent_id__0': None, 'metadata_id__0': 18, 'last_updated_ts__0': 1713422169.106019, 'context_user_id_bin__0': None, 'origin_idx__0': 0, 'state__0': '223.4', 'last_reported_ts__0': 1713422169.106019, 'old_state_id__0': 2153424468, 'context_id_bin__0': <psycopg2.extensions.Binary object at 0x7a20dd2be7f0>, 'attributes__0': None, 'context_id__0': None, 'last_changed_ts__0': None, 'context_user_id__0': None, 'entity_id__0': None, 'last_updated__0': None, 'event_id__0': None, 'context_parent_id_bin__1': None, 'attributes_id__1': 842564, 'last_changed__1': None, 'context_parent_id__1': None, 'metadata_id__1': 4, 'last_updated_ts__1': 1713422169.106196, 'context_user_id_bin__1': None, 'origin_idx__1': 0, 'state__1': '50.0', 'last_reported_ts__1': 1713422169.106196, 'old_state_id__1': 2153424469, 'context_id_bin__1': <psycopg2.extensions.Binary object at 0x7a20dd2bff60>, 'attributes__1': None, 'context_id__1': None, 'last_changed_ts__1': None, 'context_user_id__1': None, 'entity_id__1': None, 'last_updated__1': None, 'event_id__1': None, 'context_parent_id_bin__2': None, 'attributes_id__2': 842565, 'last_changed__2': None, 'context_parent_id__2': None, 'metadata_id__2': 30, 'last_updated_ts__2': 1713422169.108249, 'context_user_id_bin__2': None, 'origin_idx__2': 0, 'state__2': '223.4', 'last_reported_ts__2': 1713422169.108249, 'old_state_id__2': 2153424470, 'context_id_bin__2': <psycopg2.extensions.Binary object at 0x7a20dd2beb20> ... 622 parameters truncated ... 'origin_idx__35': 0, 'state__35': 'active', 'last_reported_ts__35': None, 'old_state_id__35': 2153424503, 'context_id_bin__35': <psycopg2.extensions.Binary object at 0x7a20d94caf10>, 'attributes__35': None, 'context_id__35': None, 'last_changed_ts__35': None, 'context_user_id__35': None, 'entity_id__35': None, 'last_updated__35': None, 'event_id__35': None, 'context_parent_id_bin__36': None, 'attributes_id__36': 1557432, 'last_changed__36': None, 'context_parent_id__36': None, 'metadata_id__36': 143, 'last_updated_ts__36': 1713422169.370342, 'context_user_id_bin__36': None, 'origin_idx__36': 0, 'state__36': 'active', 'last_reported_ts__36': None, 'old_state_id__36': 2153424504, 'context_id_bin__36': <psycopg2.extensions.Binary object at 0x7a20d94ca970>, 'attributes__36': None, 'context_id__36': None, 'last_changed_ts__36': None, 'context_user_id__36': None, 'entity_id__36': None, 'last_updated__36': None, 'event_id__36': None, 'context_parent_id_bin__37': None, 'attributes_id__37': 3641405, 'last_changed__37': None, 'context_parent_id__37': None, 'metadata_id__37': 1614, 'last_updated_ts__37': 1713422171.319873, 'context_user_id_bin__37': None, 'origin_idx__37': 0, 'state__37': '2', 'last_reported_ts__37': None, 'old_state_id__37': None, 'context_id_bin__37': <psycopg2.extensions.Binary object at 0x7a20d94c8f90>, 'attributes__37': None, 'context_id__37': None, 'last_changed_ts__37': None, 'context_user_id__37': None, 'entity_id__37': None, 'last_updated__37': None, 'event_id__37': None}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)


In database logs: 
2024-04-18T15:40:42.634371856+03:00 2024-04-18 12:40:42.634 UTC [35] ERROR:  integer out of range
2024-04-18T15:40:42.634400624+03:00 2024-04-18 12:40:42.634 UTC [35] STATEMENT:  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, context_user_id_bin, context_parent_id_bin, metadata_id) SELECT p0::CHAR(1), p1::VARCHAR, p2::CHAR(1), p3::SMALLINT, p4::TIMESTAMP WITH TIME ZONE, p5::FLOAT, p6::FLOAT, p7::TIMESTAMP WITH TIME ZONE, p8::FLOAT, p9::INTEGER, p10::INTEGER, p11::CHAR(1), p12::CHAR(1), p13::CHAR(1), p14::SMALLINT, p15::BYTEA, p16::BYTEA, p17::BYTEA, p18::INTEGER FROM (VALUES (NULL, '228.3', NULL, NULL, NULL, NULL, 1713444038.36944, NULL, 1713444038.36944, 2153571125, 842563, NULL, NULL, NULL, 0, '\x018ef13926e19fdf34656443482309e6'::bytea, NULL, NULL, 18, 0), (NULL, '49.9', NULL, NULL, NULL, NULL, 1713444038.371443, NULL, 1713444038.371443, 2153571126, 842564, NULL, NULL, NULL, 0, '\x018ef13926e39641f1f6b1421b5ff8d1'::bytea, NULL, NULL, 4, 1), (NULL, '228.3', NULL, NULL, NULL, NULL, 1713444038.371677, NULL, 1713444038.371677, 2153571127, 842565, NULL, NULL, NULL, 0, '\x018ef13926e39b31dc74f01707875aa0'::bytea, NULL, NULL, 30, 2), (NULL, '49.9', NULL, NULL, NULL, NULL, 1713444038.371821, NULL, 1713444038.371821, 2153571128, 842566, NULL, NULL, NULL, 0, '\x018ef13926e398c1c3bd84f07e46919c'::bytea, NULL, NULL, 33, 3), (NULL, '319', NULL, NULL, NULL, NULL, 1713444038.371958, NULL, 1713444038.371958, 2153571129, 842567, NULL, NULL, NULL, 0, '\x018ef13926e300d1d0bb5532acb3e279'::bytea, NULL, NULL, 24, 4), (NULL, '209', NULL, NULL, NULL, NULL, 1713444038.372101, NULL, 1713444038.372101, 2153571130, 842568, NULL, NULL, NULL, 0, '\x018ef13926e48b9813664544995a33c1'::bytea, NULL, NULL, 6, 5), (NULL, '5', NULL, NULL, NULL, NULL, 1713444038.372236, NULL, 1713444038.372236, 2153571132, 842569, NULL, NULL, NULL, 0, '\x018ef13926e488a56efe2fd5d590dc87'::bytea, NULL, NULL, 21, 6), (NULL, '401', NULL, NULL, NULL, NULL, 1713444038.372366, NULL, 1713444038.372366, 2153571133, 842570, NULL, NULL, NULL, 0, '\x018ef13926e45aaf1b855c62cb5e93ec'::bytea, NULL, NULL, 5, 7), (NULL, '53.8', NULL, NULL, NULL, NULL, 1713444038.372497, NULL, 1713444038.372497, 2153571134, 842571, NULL, NULL, NULL, 0, '\x018ef13926e43218b990dadcc895c399'::bytea, NULL, NULL, 13, 8), (NULL, '3', NULL, NULL, NULL, NULL, 1713444038.372662, NULL, 1713444038.372662, 2153571135, 842572, NULL, NULL, NULL, 0, '\x018ef13926e4f75b4191d05349ea2262'::bytea, NULL, NULL, 22, 9), (NULL, '82', NULL, NULL, NULL, NULL, 1713444038.37443, NULL, 1713444038.37443, 2153571136, 842573, NULL, NULL, NULL, 0, '\x018ef13926e600c7e718d5e6f91ce24e'::bytea, NULL, NULL, 14, 10), (NULL, '42', NULL, NULL, NULL, NULL, 1713444038.3761, NULL, 1713444038.3761, 2153571137, 842574, NULL, NULL, NULL, 0, '\x018ef13926e8e29bdd00987ec47b4ecb'::bytea, NULL, NULL, 19, 11), (NULL, '1.1', NULL, NULL, NULL, NULL, 1713444038.376307, NULL, 1713444038.376307, 2153571138, 842575, NULL, NULL, NULL, 0, '\x018ef13926e86be18a3fc58f3904b922'::bytea, NULL, NULL, 7, 12), (NULL, '239.0', NULL, NULL, NULL, NULL, 1713444038.376485, NULL, 1713444038.376485, 2153571139, 842576, NULL, NULL, NULL, 0, '\x018ef13926e819767fd43ac605ba830b'::bytea, NULL, NULL, 25, 13), (NULL, '0.0', NULL, NULL, NULL, NULL, 1713444038.37666, NULL, 1713444038.37666, 2153571140, 842577, NULL, NULL, NULL, 0, '\x018ef13926e855b13afac477b86464f6'::bytea, NULL, NULL, 15, 14), (NULL, '0', NULL, NULL, NULL, NULL, 1713444038.376812, NULL, 1713444038.376812, 2153571141, 842578, NULL, NULL, NULL, 0, '\x018ef13926e8032672644cc4aa82b47b'::bytea, NULL, NULL, 16, 15), (NULL, 'off', NULL, NULL, NULL, NULL, 1713444038.376967, NULL, 1713444038.376967, 2153571142, 1198576, NULL, NULL, NULL, 0, '\x018ef13926e8654c2f15dd2bead4f03b'::bytea, NULL, NULL, 88, 16), (NULL, 'off', NULL, NULL, NULL, NULL, 1713444038.377116, NULL, 1713444038.377116, 2153571143, 1198577, NULL, NULL, NULL, 0, '\x018ef13926e9ea96a5fd9d7787504432'::bytea, NULL, NULL, 89, 17), (NULL, 'off', NULL, NULL, NULL, NULL, 1713444038.377258, NULL, 1713444038.377258, 2153571144, 1198578, NULL, NULL, NULL, 0, '\x018ef13926e93c2ea83bbd0ac7a988a1'::bytea, NULL, NULL, 90, 18), (NULL, 'on', NULL, NULL, NULL, NULL, 1713444038.377398, NULL, 1713444038.377398, 2153571145, 1198579, NULL, NULL, NULL, 0, '\x018ef13926e92cdd9d4e646714ccf9f6'::bytea, NULL, NULL, 91, 19), (NULL, 'off', NULL, NULL, NULL, NULL, 1713444038.377538, NULL, 1713444038.377538, 2153571146, 1198580, NULL, NULL, NULL, 0, '\x018ef13926e934776a7207db7a3c4e5d'::bytea, NULL, NULL, 92, 20), (NULL, 'on', NULL, NULL, NULL, NULL, 1713444038.377706, NULL, 1713444038.377706, 2153571147, 1198581, NULL, NULL, NULL, 0, '\x018ef13926e98b6b271ae67b9248c7f3'::bytea, NULL, NULL, 93, 21), (NULL, 'on', NULL, NULL, NULL, NULL, 1713444038.379747, NULL, 1713444038.379747, 2153571148, 1198582, NULL, NULL, NULL, 0, '\x018ef13926ebb34d7ba5f747d158821f'::bytea, NULL, NULL, 94, 22), (NULL, 'on', NULL, NULL, NULL, NULL, 1713444038.380414, NULL, 1713444038.380414, 2153571149, 1198583, NULL, NULL, NULL, 0, '\x018ef13926ec00e7009cec5992371ce5'::bytea, NULL, NULL, 95, 23), (NULL, '0', NULL, NULL, NULL, NULL, 1713444038.380884, NULL, 1713444038.380884, 2153571150, 842587, NULL, NULL, NULL, 0, '\x018ef13926ec0d4d1ae90f5204b2f66d'::bytea, NULL, NULL, 26, 24), (NULL, '0', NULL, NULL, NULL, NULL, 1713444038.381411, NULL, 1713444038.381411, 2153571151, 1198584, NULL, NULL, NULL, 0, '\x018ef13926ed189a94ed43ab083cc51a'::bytea, NULL, NULL, 20, 25), (NULL, '270', NULL, NULL, NULL, NULL, 1713444038.382291, NULL, 1713444038.382291, 2153571152, 1133697, NULL, NULL, NULL, 0, '\x018ef13926ee1dd1f7c34e044e6bf844'::bytea, NULL, NULL, 3, 26), (NULL, 'off', NULL, NULL, NULL, NULL, 1713444038.383271, NULL, 1713444038.383271, 2153571153, 1198585, NULL, NULL, NULL, 0, '\x018ef13926efbe48d6ab5ca6ede62d40'::bytea, NULL, NULL, 96, 27), (NULL, 'on', NULL, NULL, NULL, NULL, 1713444038.383784, NULL, 1713444038.383784, 2153571154, 1198586, NULL, NULL, NULL, 0, '\x018ef13926ef1b544d2f99b0bbbd9550'::bytea, NULL, NULL, 97, 28), (NULL, 'off', NULL, NULL, NULL, NULL, 1713444038.384314, NULL, 1713444038.384314, 2153571155, 1198587, NULL, NULL, NULL, 0, '\x018ef13926f00b010f47be71403cdeed'::bytea, NULL, NULL, 98, 29), (NULL, '0.76', NULL, NULL, NULL, NULL, 1713444040.591429, NULL, 1713444040.591429, 2153571131, 1093276, NULL, NULL, NULL, 0, '\x018ef1392f8e971741c2eb77f49d4c16'::bytea, NULL, NULL, 55, 30)) AS imp_sen(p0, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, sen_counter) ORDER BY sen_counter RETURNING states.state_id, states.state_id AS state_id__1

Additional information

No response

[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)}}