Re: Duplicate key violation on upsert

On 3/25/20 5:23 PM, Matt Magoffin wrote:
>
>> On 23/03/2020, at 1:10 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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(dot)klaver(at)aklaver(dot)com