On 3/21/20 2:45 PM, Matt Magoffin wrote:
On 22/03/2020, at 8:11 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
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
Sorry for the vagueness in my OP, I was trying to make it easier to
read. The VALUES are for individual single column values, so a single
possible row to insert/update.
So what you’ve outlined is basically what I thought should be happening.
Namely, there can be only one row that will be inserted/updated. I am
wondering if I should re-create the da_datum_x_acc_idx index without
UNIQUE? I had it as UNIQUE to optimise the type of queries that make use
of that index… but I did a little bit of testing using a non-UNIQUE
index and those queries appear to execute around the same time as with
the UNIQUE index. I just wasn’t sure if that would just be masking some
other problem in my setup.
Well if the combination of values do not need to be UNIQUE then imposing
UNIQUE on them, in my mind, is an unnecessary constraint. Though it
would be good to know why the constraint is being tripped.
Is there a chance the BEFORE trigger functions are doing something that
could be leading to the error?
In the error log is there a line with the actual values that failed?
— m@
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
