From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Matt Magoffin <postgresql(dot)org(at)msqr(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Duplicate key violation on upsert |
Date: | 2020-03-20 19:10:34 |
Message-ID: | 00a020ca-f2db-2dd7-2aa6-e3ef78e7ba68@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/20/20 11:34 AM, Matt Magoffin wrote:
>
>
>> On 21/03/2020, at 4:00 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 3/20/20 2:17 AM, Matt Magoffin wrote:
>>> Hello,
>>> Indexes:
>>> "da_datum_pkey" UNIQUE, btree (node_id, ts, source_id) CLUSTER,
>>> tablespace "solarindex"
>>> "da_datum_reverse_pkey" UNIQUE, btree (node_id, ts DESC,
>>> source_id), tablespace "solarindex"
>>> "da_datum_x_acc_idx" UNIQUE, btree (node_id, source_id, ts DESC,
>>> jdata_a) WHERE jdata_a IS NOT NULL, tablespace "solarindex"
>>> The error/query looks like:
>>> ERROR: duplicate key value violates unique constraint
>>> “_hyper_1_1931_chunk_da_datum_x_acc_idx"
>>
>> What is the above index UNIQUE over?
>>
>> What is da_datum_x_acc_idx index below indexed over?
>
> 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."
>
> — m@
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2020-03-20 19:17:16 | Re: PG12 autovac issues |
Previous Message | Andres Freund | 2020-03-20 19:03:17 | Re: PG12 autovac issues |