Re: Duplicate key violation on upsert - Mailing list pgsql-general

On 3/21/20 11:05 AM, Adrian Klaver wrote:
> On 3/20/20 1:32 PM, Matt Magoffin wrote:
>>
>>> On 21/03/2020, at 8:10 AM, Adrian Klaver <adrian.klaver@aklaver.com 
>>> <mailto:adrian.klaver@aklaver.com>> wrote:
>>>
>>>> The _hyper_1_1931_chunk_da_datum_x_acc_idx index has the same 
>>>> definition as the da_datum_x_acc_idx above (it is defined on a child 
>>>> table). That is, they are both essentially:
>>>> UNIQUE, btree (node_id, source_id, ts DESC, jdata_a) WHERE jdata_a 
>>>> IS NOT NULL
>>>> The da_datum_pkey index is what the ON CONFLICT cause refers to, so 
>>>> (node_id, ts, source_id) is UNIQUE as well.
>>>
>>> Hmm, wonder if you are getting bit by this?:
>>>
>>> https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CONFLICT
>>>
>>> "INSERT with an ON CONFLICT DO UPDATE clause is a “deterministic” 
>>> statement. This means that the command will not be allowed to affect 
>>> any single existing row more than once; a cardinality violation error 
>>> will be raised when this situation arises. Rows proposed for 
>>> insertion should not duplicate each other in terms of attributes 
>>> constrained by an arbiter index or constraint.”
>>
>> I’m not sure I’m wrapping my head around this. The INSERT affects 1 
>> row as the unique values (node_id, ts, source_id) are specified in the 
>> statement. Is it possible that da_datum_x_acc_idx is used as the 
>> arbiter index in this situation, rather than da_datum_pkey (that I 
>> intended), and you’re saying that the jdata_a column is getting 
>> updated twice, first in the INSERT and second in the DO UPDATE, 
>> triggering the duplicate key violation?
> 
> I was thinking more about this:
> 
> "INSERT INTO solardatum.da_datum(ts, node_id, source_id, posted, 
> jdata_i, jdata_a, jdata_s, jdata_t)
>      VALUES (…) ..."
> 
> from your OP. Namely whether it was:
> 
> VALUES (), (), (), ...
> 
> and if so there were values in the (),(),() that duplicated each other.
> 
> As to the second part of your response, ON CONFLICT does one of either 
> INSERT or UPDATE. If:
> 
> 1) There is no conflict for ON CONFLICT (node_id, ts, source_id) then 
> the INSERT proceeds.
> 
> 2) If there is a conflict then an UPDATE occurs using the SET values.
> 
> Now just me working through this:
> 
> da_datum_pkey       = (node_id, ts, source_id)
> da_datum_x_acc_idx  = (node_id, source_id, ts DESC, jdata_a)
> 
> If 1) from above applies then da_datum_x_acc_idx will not be tripped as 
> the only way that could happen is if the node_id, ts, source_id was the 
> same as an existing row and that can't be true because the PK over the 
> same values passed.

Well the below is complete rot. If you are UPDATEing then you are not 
creating a duplicate row, just overwriting a value with itself.

> 
> If 2) from above happened then you are trying to UPDATE a row with 
> matching PK values(node_id, ts, source_id). Now it is entirely possible 
> that since you are not testing for constraint violation on (node_id, 
> source_id, ts DESC, jdata_a) that you be doing SET jdata_a = 
> EXCLUDED.jdata_a, using a value that would trip da_datum_x_acc_idx
> 
>>
>> — m@
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com