Re: Duplicate key violation on upsert

On 3/20/20 11:34 AM, Matt Magoffin wrote:
> 
> 
>> On 21/03/2020, at 4:00 AM, Adrian Klaver <adrian.klaver@aklaver.com 
>> <mailto:adrian.klaver@aklaver.com>> wrote:
>>
>> On 3/20/20 2:17 AM, Matt Magoffin wrote:
>>> Hello,
>>> Indexes:
>>>     "da_datum_pkey" UNIQUE, btree (node_id, ts, source_id) CLUSTER, 
>>> tablespace "solarindex"
>>>     "da_datum_reverse_pkey" UNIQUE, btree (node_id, ts DESC, 
>>> source_id), tablespace "solarindex"
>>>     "da_datum_x_acc_idx" UNIQUE, btree (node_id, source_id, ts DESC, 
>>> jdata_a) WHERE jdata_a IS NOT NULL, tablespace "solarindex"
>>> The error/query looks like:
>>> ERROR: duplicate key value violates unique constraint 
>>> “_hyper_1_1931_chunk_da_datum_x_acc_idx"
>>
>> What is the above index UNIQUE over?
>>
>> What is da_datum_x_acc_idx index below indexed over?
> 
> 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."


> 
> — m@
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com