Re: Duplicate key violation on upsert

2020-03-26 Thread Matt Magoffin
> On 27/03/2020, at 5:26 AM, Adrian Klaver wrote: > > Well morning and coffee helped some, but not enough to offer blinding > insight. Reviewing the function above, the TimescaleDB insert block function > and the overview of the TimescaleDB hypertable architecture leads me to > believe there

Re: Duplicate key violation on upsert

2020-03-26 Thread Adrian Klaver
On 3/25/20 5:23 PM, Matt Magoffin wrote: On 23/03/2020, at 1:10 PM, Adrian Klaver > wrote: So the query is in the function solardatum.store_datum()? If so what is it doing? Yes. This function first performs the INSERT INTO the solardatum.da_datum table tha

Re: Duplicate key violation on upsert

2020-03-25 Thread Adrian Klaver
On 3/25/20 5:23 PM, Matt Magoffin wrote: On 23/03/2020, at 1:10 PM, Adrian Klaver > wrote: So the query is in the function solardatum.store_datum()? If so what is it doing? Yes. This function first performs the INSERT INTO the solardatum.da_datum table tha

Re: Duplicate key violation on upsert

2020-03-25 Thread Matt Magoffin
> On 23/03/2020, at 1:10 PM, Adrian Klaver wrote: > > So the query is in the function solardatum.store_datum()? > > If so what is it doing? Yes. This function first performs the INSERT INTO the solardatum.da_datum table that we’re discussing here; then it inserts into two different tables. I

Re: Duplicate key violation on upsert

2020-03-22 Thread Adrian Klaver
On 3/22/20 2:48 PM, Matt Magoffin wrote: On 23/03/2020, at 9:44 AM, Adrian Klaver > 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 fail

Re: Duplicate key violation on upsert

2020-03-22 Thread Matt Magoffin
> On 23/03/2020, at 9:44 AM, Adrian Klaver 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 li

Re: Duplicate key violation on upsert

2020-03-22 Thread Adrian Klaver
On 3/21/20 2:45 PM, Matt Magoffin wrote: On 22/03/2020, at 8:11 AM, Adrian Klaver > wrote: 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.

Re: Duplicate key violation on upsert

2020-03-21 Thread Matt Magoffin
> On 22/03/2020, at 8:11 AM, Adrian Klaver wrote: > >> 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

Re: Duplicate key violation on upsert

2020-03-21 Thread Adrian Klaver
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 > 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

Re: Duplicate key violation on upsert

2020-03-21 Thread Adrian Klaver
On 3/20/20 1:32 PM, Matt Magoffin wrote: On 21/03/2020, at 8:10 AM, Adrian Klaver > 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 essen

Re: Duplicate key violation on upsert

2020-03-20 Thread Matt Magoffin
> On 21/03/2020, at 8:10 AM, Adrian Klaver 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) WH

Re: Duplicate key violation on upsert

2020-03-20 Thread Adrian Klaver
On 3/20/20 11:34 AM, Matt Magoffin wrote: On 21/03/2020, at 4:00 AM, Adrian Klaver > 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_re

Re: Duplicate key violation on upsert

2020-03-20 Thread Matt Magoffin
> On 21/03/2020, at 4:00 AM, Adrian Klaver 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), >>

Re: Duplicate key violation on upsert

2020-03-20 Thread Adrian Klaver
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