From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Matt Magoffin <postgresql(dot)org(at)msqr(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Duplicate key violation on upsert |
Date: | 2020-03-26 04:28:02 |
Message-ID: | 4372965a-188b-592c-8f08-92065f90efb7@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
I told see anything wrong at first glance, but is getting late here. I
will take another look in the morning.
>
>> 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 values for (node_id, ts, source_id, jdata_a) as they compromise the
UNIQUE values for da_datum_pkey and da_datum_x_acc_idx.
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan E. Panchenko | 2020-03-26 07:07:48 | Re: PostgreSQL 13: native JavaScript Procedural Language support ? |
Previous Message | Michael Paquier | 2020-03-26 03:52:10 | Re: PG12 autovac issues |