Re: Duplicate key violation on upsert

On 3/20/20 2:17 AM, Matt Magoffin wrote:
> Hello,
> 
> I am experiencing a duplicate key violation in Postgres 9.6 on occasion 
> for one particular query, and I’m wondering where I’m going wrong. My 
> table looks like this:
> 
>                        Table "solardatum.da_datum"
>    Column   |           Type           | Collation | Nullable | Default
> -----------+--------------------------+-----------+----------+---------
>   ts        | timestamp with time zone |           | not null |
>   node_id   | bigint                   |           | not null |
>   source_id | character varying(64)    |           | not null |
>   posted    | timestamp with time zone |           | not null |
>   jdata_i   | jsonb                    |           |          |
>   jdata_a   | jsonb                    |           |          |
>   jdata_s   | jsonb                    |           |          |
>   jdata_t   | text[]                   |           |          |
> 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"
> Triggers:
>      aa_agg_stale_datum BEFORE INSERT OR DELETE OR UPDATE ON 
> solardatum.da_datum FOR EACH ROW EXECUTE 
> PROCEDURE solardatum.trigger_agg_stale_datum()
>      ts_insert_blocker BEFORE INSERT ON solardatum.da_datum FOR EACH ROW 
> EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
> 
> 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?


>    Where: SQL statement "INSERT INTO solardatum.da_datum(ts, node_id, 
> source_id, posted, jdata_i, jdata_a, jdata_s, jdata_t)
> VALUES (…)
> ON CONFLICT (node_id, ts, source_id) DO UPDATE
> SET jdata_i = EXCLUDED.jdata_i,
> jdata_a = EXCLUDED.jdata_a,
> jdata_s = EXCLUDED.jdata_s,
> jdata_t = EXCLUDED.jdata_t,
> posted = EXCLUDED.posted
> RETURNING (xmax = 0)"
> 
> I am using the TimescaleDB extension so there are child tables 
> inheriting from this main table and that’s why the reported index name 
> differs from the definition shown above. I’m not sure if the extension 
> is the problem, so I thought I’d start here to see if I’ve configured 
> something wrong or my expectations on how the upsert should work is 
> wrong. My expectation was that basically the insert would never fail 
> from a duplicate key violation.
> 
> The error always references the da_datum_x_acc_idx index, which is a 
> partial index with jdata_a added as a covering column… that is, it’s 
> only in the index so I can get some index-only results with that column. 
> Is the partial index possibly an issue in this configuration?
> 
> Thanks for any insight,
> Matt


-- 
Adrian Klaver
adrian.klaver@aklaver.com