On 3/21/20 2:45 PM, Matt Magoffin wrote:
>
>> On 22/03/2020, at 8:11 AM, Adrian Klaver <adrian.klaver@aklaver.com
>> <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com