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(dot)klaver(at)aklaver(dot)com