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-23 00:10:10 |
Message-ID: | ff653668-3fad-da5c-b26a-addd2ea0f87e@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/22/20 2:48 PM, Matt Magoffin wrote:
>
>> On 23/03/2020, at 9:44 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>> Is there a chance the BEFORE trigger functions are doing something
>> that could be leading to the error?
>>
>> In the error log is there a line with the actual values that failed?
>
> The error log does not show the literal values, no. Here is a literal
> example from the logs:
>
> 2020-03-20 19:51:11 NZDT [15165]: [6-1] ERROR: duplicate key value
> violates unique constraint "_hyper_1_1931_chunk_da_datum_x_acc_idx"
> 2020-03-20 19:51:11 NZDT [15165]: [7-1] CONTEXT: SQL statement "INSERT
> INTO solardatum.da_datum(ts, node_id, source_id, posted, jdata_i,
> jdata_a, jdata_s, jdata_t)
> VALUES (ts_crea, node, src, ts_post, jdata_json->'i',
> jdata_json->'a', jdata_json->'s',
> solarcommon.json_array_to_text_array(jdata_json->'t'))
> 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)"
> PL/pgSQL function solardatum.store_datum(timestamp with time
> zone,bigint,text,timestamp with time zone,text,boolean) line 10 at SQL
> statement
> 2020-03-20 19:51:11 NZDT [15165]: [8-1] STATEMENT: select * from
> solardatum.store_datum($1, $2, $3, $4, $5) as result
So the query is in the function solardatum.store_datum()?
If so what is it doing?
And could you capture the values and pass them to a RAISE NOTICE?
>
> As for the BEFORE triggers, the solardatum.trigger_agg_stale_datum one
> does an INSERT into a different table and a SELECT from this same table.
> The _timescaledb_internal.insert_blocker one is part of the TimescaleDB
> extension which looks like it wouldn’t have an impact to this issue, but
> the source of that is
>
> https://github.com/timescale/timescaledb/blob/91fe723d3aaaf88b53ffffebf8adc3e16a68ec45/src/hypertable.c#L1359
>
> — m@
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | pabloa98 | 2020-03-23 00:35:33 | Re: Could postgres12 support millions of sequences? (like 10 million) |
Previous Message | Adrian Klaver | 2020-03-23 00:06:49 | Re: Could postgres12 support millions of sequences? (like 10 million) |