I accidentally enabled the tag historian on a few tags in my project. I’ve since corrected this and disabled historian on them. However, they still show up in my historical tag browse tree and I’d like them gone!
In the past I’ve accomplished this by deleting all the sqlt… tables in the database and restarting the historian module. I’d like to do this more surgical this time. If I could just filter them out of the tag browse tree that would work as well (though some are sole contents of folders that also shouldn’t be visible).
I don’t care about the data that was historized accidentally - it can either be deleted or left to rot in the stlt_data_… tables until it gets pruned (there isn’t that many rows). Most of these tags already have a timestamp in their “retired” colum (sqlth_te table) and I need to disable the historian from the remainder that aren’t retired yet.
I’ve tried deleting the rows from the sqlt_data_… tables , deleting their row from sqlth_te, and restarting the historian module. This didn’t seem to work.
What is the correct way to do this?
EDIT: I used (I’m using PostgreSQL):
delete FROM sqlt_data_1_2022_07
where tagid in (SELECT id FROM sqlth_te where retired is not null)
This should be sufficient I think. I often clean things up with something along the lines of
delete from sqlt_data_X_XXXX_XX
inner join sqlth_te
where tagid = id and retired is not null
`delete from sqlth_te where retired is not null`
I was thinking the same thing, Pascal, but the sqlth_te table doesn’t tell you if the tag has history enabled or not. That’s stored somewhere in the tag database. Your SQL code will delete history where the tags have been retired but OP’s tags are still active. It’s only history has been disabled.
While we’re on the topic - does anyone know what the “th_te” in the table name means?
Still, it’s the same concept he tried, and it usually works for me. Deleting entries in the historian tables should be sufficient to clean things up.
‘tag historian’ and ‘table entry’ ?
Nah, the table has nothing to do with history!
Maybe the better question is where does the tag browse tree decide what tags should be shown in historical mode? From the sqltxxx tables or somewhere else?
When a Tag History is enabled an entry is made in the sqlth_te table, when the History is disabled the tag is marked as “retired” (this also occurs if the tag name is changed, so anything that results in a new tag path). The Tag Browse Tree pulls all records from this table, it doesn’t differentiate between retired and non-retired tags. The only way (that I have found) to get the tags to not show up in the component is to remove the record from
sqlth_te. You should also remove all of those records from the actual partition tables as well, although strictly not required its just good practice.
As far as I can tell, the table only exists as part of the Tag Historian. Tags that have never had history enabled will not be included in the table.
Ah! That makes sense then. @pascal.fragnoud is correct.
Thank you both.
It looks like the earlier deletions worked, I just hadn’t turned off tag history on the tags like I thought I did.