Daily "Beginning prune of ..." log entries

I am running the SQL logger plugin primarily for detecting “error” messages in Event log and running triggers based on this.

For this reason (I.e. no interest of logging anything in sqlite database), I always had ALL checkboxes in SQL logger plugin config DESELECTED.

Nevertheless, since upgrading to Indigo 2022.2.0 I am getting daily Indigo Event log entries about
Beginning prune of old variable history data
Beginning prune of old event log history data
Beginning prune of old device history data

I checked the python script and it appears to me that these pruning sections should not run unless configured as such with certain day entries etc. Since I am not logging anything in the database there should be nothing to prune.

What is going on here?

[jay (support)]

We'll look into it.

[jay (support)]

Those are just log lines - it's not actually attempting to do anything. We'll move the log lines inside the test so they don't show if there is nothing being logged (in the next release).

It looks like the “Beginning prune of …” section of the plugin is triggered because the plugin defaults to the built-in pruning preference setting defaults (which are >0), even so in the Config dialog none of the boxes are selected to store anything in the database.

I believe it is actually doing the pruning every 24 hours because the trimDate is earlier than the current date.

I can prevent the pruning by first turning on the “Store in database” setting in the plugin Config dialog, then selecting Auto prune Never and then turning off the “Store in database setting”.


Unrelated to this I am getting errors storing device histories of GhostXML Devices with too many columns on device_history

SQL Logger Error One or more failures updating device history; see the debug log for details

[matt (support)]

Indigo 2022.2.1 is now available and will prevent pruning if Indigo isn't logging history to a specific table.

I am really not a database expert; my abilities consist of opening the server and querying information by indigo ID... Question: is the prune retroactive? I only noticed that the prune option was not checked after I ran into an issue in Postgres where indigo variables that I created were not being added to the database; I could do a search on the ID and the search SAID there was a PKey and id_seq and data table, but when I tried to open them, I got "object not found" error. So I then looked at the total list of tables and there are THOUSANDS of table entries with _ID# that are all empty and not associated with any existing device or variable when I search that ID in indigo.

So 3 questions:

1. now that I have checked the "prune deleted" box and set the time limit at 1 month, will all those empty unassociated tables be removed tonight?
2. Is the reason that I can no longer add variables to Postgres because I hit some kind of database limit?
3. if the answer to 1 and 2 are yes, will the tables for the new variables be added once the deletion of the unassociated tables occurs?

[matt (support)]

I just glanced at the code in the plugin to refresh my memory on how this works.

There are a couple of different preferences. the "Prune" preferences delete rows in a table based on how old they are. That logic should automatically occur after a minute or so if you make a UI change.

The other preference is a UI checkbox to "Auto delete unused" (for either devices or variables) which removes the entire table for a given object if it is deleted. However, from looking at the code this logic only executes when a variable or device is deleted. So it isn't going to cleanup all the unused/empty variable tables you currently have since it was probably not checked when those variables were deleted in the Indigo Server. We should look into improving this logic. In the mean time you would either need to manually delete those tables or just start over with a new Postgres database (or drop all the tables in your current database). The plugin will automatically create the new tables that are needed for your current Indigo database (although all the historical rows will be missing, of course).