I’m creating the cleanup script for the home assistant’s history database (PostgreSQL). In my setup it grows for and 1GB every day (which is normal).
During cleanup I’m removing unchanged states and associated attributes.
After everything is done I’m removing the orphaned attributes (which doesn’t have associated state) using following query:
where not exists(select attributes_id
where states.attributes_id = state_attributes.attributes_id)
However, each and evry time it fails with FK violation. The reason is : state_attributes record created before the associated state record is created.
The suggestion is: create state_attributes and associated state record using the single transaction, which will improve the data consistency for HA.