Re: Duplicate key violation on upsert

> On 21/03/2020, at 8:10 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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 <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?

— m@