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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx
