FRQ: add transaction to the states and state_attributes creation process

Development
1

Hello community.
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:

delete
from state_attributes
where not exists(select attributes_id
                 from states
                 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.

1 Like