Search Postgresql Archives

Re: Duplicate key violation on upsert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux