device states initialized as integer

for Matt & Jay:

with

Code: Select all

<ValueType>Number</ValueType>

the device states get initialized as integer.
When setting them with a float number (updatedevicestateonserver() indigo THEN changes the sql data type to REAL.
That transaction takes a long time in the sql database as all previous entries must be converted to real. When the database is very large (20Gbyte in my case) indigo timeouts waiting for the transaction to finish.
Then many things go wrong (20 pages of red error messages) and the only option is to stop the sqllogger plugin and restart it with other plugins disabled to stop writing to the database. After all states have been converted to REAL I can restart the other plugins and everything runs fine again.

Is there anyway to
1. initialize the device/state as real and not integer
2. increase the sql wait-time
?

thanks

Karl

[matt (support)]

Hi Karl,

Are you sure the initial value you are giving it is a real and not integer? If so then I'll look into adding a way to specify (in the XML) integer versus real. Unfortunately, I cannot think of a short term workaround.

yes almost 99.99% it is a float

Code: Select all

chList.append({u"key":key,"value":changedOnly[devId][key][0], u"decimalPlaces":changedOnly[devId][key][1]})
...
dev.updateStatesOnServer(chList)
...

with changedOnly[devId][key][0] = float value


[matt (support)]

Gotcha. I'll add it to my list to look into.

Karl, I've run into this many times -- usually with the WUnderground plugin simply because that is my largest table and thus prone to time out. One main issue is that the SQL Logger uses an ALTER TABLE ALTER COLUMN style change... this works great on small data sets but is horrible for large tables. It is far more efficient (in Postgre) to actually recreate the table with the new structure, drop the old table, then rename the newly created one.

I've been playing a TINY bit with doing this operation in SQL Logger as a test... basic algorithm is:
1. ALTER TABLE [tablename] RENAME [oldcol] TO [oldcoltemp]
2. ALTER TABLE [tablename] ADD COLUMN [colname] [type]
3. ... rebuild a temp table selecting the temp old column into the new data type col
4. DROP [tablename]
5. ALTER TABLE [tablenametemp] TO [tablename]

This has a huge advantage in that #1 & 2 will not cause an exclusive lock on the table and not require a full table scan/rewrite since it is a metadata change only. Then 3 is a query and writes sequentially to the new table, a much faster process in Postgre than an update to a row (and every row in the table at that). The downside is concurrency... I haven't figured out a "good" way to queue up all of the state changes that come in during this whole process short of adding yet another temp table into the mix or storing in memory. SO, still pondering.

Thoughts from anyone?

Adam

Another observation. It looks as if the states are initialized with integer value= 0


Sent from my iPhone using Tapatalk