Find history partition size dynamically?

Ignition
1

I'm trying to write a bit of code that can be run on a few different gateways without issue, but it needs to know the history partition size of the gateway to be useful and these gateways are all configured differently. I could hard-code a table of partition sizes per gateway name and look them up, but I'm hoping there might be a way to dynamically find a history providers partition size from a script so I don't inevitably break something when someone changes the partition size a year or more from now and forgets about this script.

I dug through all the System tags and didn't find anything like I wanted.

Are there any hidden API calls that could make this happen?

2

How dirty are you willing to get ?

3

All of our servers are either monthly or weekly so far ... I did the lazy detection based on how the current partition name was formatted, either sqlt_data_1_yyyyMMdd or sqlt-data_1_yyyy_MM. This will explode if we ever go to a partition size other than weekly or monthly, but I have more confidence in that particular risk.

Did you have a better idea? I was contemplating just looking at the (end_time - start_time) size of the current partition and guessing from that...

4

I had nothing, but I'm home alone on kid watch duty. I have beer and nothing to do.

1 Like
5

I'd do this. From a voice of experience having looked at this area of the code...
You're in for a world of hurt trying to retrieve it from scripting.

6

Well... Not that this isn't an annoying bit of pain, also. Peeking at our current partition table I was just reminded that there is some sort of partition fragmentation bug afflicting us. The same name is repeated over and over with adjacent non-overlapping times... So, I'm really looking for this:

SELECT MAX(end_time)-MIN(start_time) FROM sqlth_partitions 
WHERE pname = (SELECT pname FROM sqlth_partitions WHERE ? BETWEEN start_time AND end_time LIMIT 1)

But ... yeah ...

7

No sane way to query the sqlite config DB? It's right there in TAGHISTORIANPROVIDERSETTINGS

Just scan the ./data/db/autobackup directory to find the most recent inactive one, open it up and run a few selects... :slight_smile: (this would not work both for risk/sanity issues, but also since our sqlite DB is getting biggish at 400+MB and would be a heavy thing to open)

8

Nope.

Perhaps we should go for the step back of the XY problem here :slight_smile: