> On 21/03/2020, at 8:10 AM, Adrian Klaver <adrian.kla...@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 > <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@
