On 3/25/20 5:23 PM, Matt Magoffin wrote:
On 23/03/2020, at 1:10 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
So the query is in the function solardatum.store_datum()?
If so what is it doing?
Yes. This function first performs the INSERT INTO the
solardatum.da_datum table that we’re discussing here; then it inserts
into two different tables. If it helps, the actual SQL is available here:
https://github.com/SolarNetwork/solarnetwork-central/blob/4fa585929a5526187ade0e842c809837647c6a41/solarnet-db-setup/postgres/postgres-init-generic-datum-x-functions.sql#L203-L242
Well morning and coffee helped some, but not enough to offer blinding
insight. Reviewing the function above, the TimescaleDB insert block
function and the overview of the TimescaleDB hypertable architecture
leads me to believe there is some sort of conflict between the
solarnetwork functions and the TimescaleDB hypertable actions. It is a
wishy-washy answer as I do not understand the TimescaleDB architecture
well enough. You might want to reach to the TimescaleDB community to see
if they can offer any further insight.
And could you capture the values and pass them to a RAISE NOTICE?
It would take me some time to get that change deployed. If I was able
to, what information do you think would be helpful here, e.g. that
jdata_a is NULL or not, or something else?
The duplicate key violation occurs infrequently, and it does seem
appropriate to drop the UNIQUE constraint on the da_datum_x_acc_idx
given uniqueness is really only wanted on (node_id, ts, source_id). As
long as I can confirm that query performance doesn’t decrease, I’d like
to recreate the index without UNIQUE. Then I’m hoping this problem,
whatever the cause, goes away.
— m@
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
