core: Duplicate key value violates unique constraint "state_attributes_pkey"

The problem

At some point recently, not sure exactly which upgrade. The following errors started popping up in the logs by the 1000s. It seems to only effect certain entities. Basically any time this error happens a piece of event data is not stored.

Unhandled database error while processing task StatesContextIDMigrationTask(): 
(raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) 
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "state_attributes_pkey" DETAIL: Key (attributes_id)=(5729439) already exists.

Unhandled database error while processing task StatesContextIDMigrationTask(): 
(raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) 
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "state_attributes_pkey" DETAIL: Key (attributes_id)=(5729440) already exists.

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "state_attributes_pkey"
DETAIL:  Key (attributes_id)=(5729440) already exists.

How might I begin to debug/fix this?

Thanks!

What version of Home Assistant Core has the issue?

core-2023.4.6

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

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

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

 Logger: homeassistant.components.recorder.entity_registry
Source: components/recorder/entity_registry.py:56
Integration: Recorder
First occurred: April 30, 2023 at 10:49:44 PM (3 occurrences)
Last logged: April 30, 2023 at 10:51:02 PM

    Cannot rename entity_id `sensor.chinese_air_pollution_level` to `sensor.nyc_air_pollution_level` because the states meta manager is not yet active
    Cannot rename entity_id `sensor.chinese_air_quality_index` to `sensor.nyc_air_quality_index` because the states meta manager is not yet active
    Cannot rename entity_id `sensor.chinese_main_pollutant` to `sensor.nyc_main_pollutant` because the states meta manager is not yet active

Additional information

No response

About this issue

Original URL
State: closed
Created a year ago
Comments: 17 (6 by maintainers)

Most upvoted comments

I’m getting similar error some days after migrating from sqlite to postgres. During migration maybe some records failed to be inserted into postgres, and not sure why the serial sequence looks pointing to old record, after sequence grows for some time, the generated id will finally be same with a new record, and duplication happens.

I fixed by resetting each table’s serial sequence to max(id) by running SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id_column'), MAX(id_column)) FROM table_name;

Hope this could be helpful.

radaiming on Jul 30, 2023

I’m getting similar error some days after migrating from sqlite to postgres. During migration maybe some records failed to be inserted into postgres, and not sure why the serial sequence looks pointing to old record, after sequence grows for some time, the generated id will finally be same with a new record, and duplication happens.

I fixed by resetting each table’s serial sequence to max(id) by running SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id_column'), MAX(id_column)) FROM table_name;

Hope this could be helpful.

Same as @OverDodo , just migrated to Postgres and it resolved my issues. 😃 Thanks

PentaSX on Jan 26, 2024

Also the first rule if corruption is suspected

https://wiki.postgresql.org/wiki/Corruption#VITALLY_IMPORTANT_FIRST_RESPONSE

bdraco on May 3, 2023