Manually clean up sqlth_te table?

Ignition
1

Somebody on my team set up several thousand tags and configured them for history when they should not have. I can easily identify the records in my PostgreSQL database, and the tags themselves have been deleted from Ignition. Is there any reason I should not manually clean up the records in my PostgreSQL database?

2

I've had to manually excise just portions of data before; wholesale deletion should be relatively easier.

Get familiar with the following schema reference, and probably good to take a backup before you start deleting anything:

EDIT: some interesting considerations in case you plan to also remove the actual data and not just the sqlth_te entries:

2 Likes
3

I think I'm fine with just letting the data fall off when the partitions are pruned (I'm only keeping data a short time (30 days) and my partitions are for a single day).

Thanks for the info, Felipe_CRM.

4

Bear in mind the pruning will only delete the data in the sqlth_data_XXXX tables. But sadly sometimes even after tag deletion, there can remain entries in the sqlth_te table.
This is average as your users may still find those "orphaned" tags when they use the Power Chart historical tag browser.
So you might still want to manually clean up the database if thats the case.

2 Likes
5

You can query your partitions for distinct tag IDs that have data, and put that on the left of a left join with sqlth_te, then filter for nulls on the left to find the tag IDs that have no data.

2 Likes
6

This may be a use case for the Tag History Transfer Tool in the Ignition Exchange:

7

I appreciate all the suggestions. I ran this query against my database and Identified tags that don't have any history from the past 5 days:

SELECT *
FROM "sqlth_te"
WHERE "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240716")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240715")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240714")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240713")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240712");

I know I'm okay with getting rid of all these rows from sqlth_te. Therefore, if I change SELECT * to DELETE, I believe I should accomplish what I want:

DELETE
FROM "sqlth_te"
WHERE "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240716")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240715")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240714")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240713")
  AND "id" NOT IN (SELECT DISTINCT "tagid" FROM "sqlt_data_1_20240712");
8

Looks good, but take a good database backup first.

3 Likes