> On 22/03/2020, at 8:11 AM, Adrian Klaver <adrian.kla...@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. — m@
