Timescaledb: UPSERT in CTE causes error/crash

[jgranstrom]

Doing an upsert with INSERT ... ON CONFLICT ... on a hypertable works well for basic queries but fails when inside a common table expression.

Tested with:

• timescale 0.6, pg 9.5
• timescale 0.8, pg 10
• timescale 0.9, pg 10.3

All using the official docker images.

Same symptoms for all tested versions, data or no data, fresh table or old table, and for DO NOTHING and DO UPDATE respectively.

To reproduce:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- create table
CREATE TABLE IF NOT EXISTS test_hyper (
   metric_id int not null,
   time timestamptz not null,
   value numeric not null
);

-- create hypertable and unique index
select create_hypertable('test_hyper', 'time');
create unique index test_index on test_hyper(metric_id, time DESC);

-- Case 1: insert cte using unique index inference for conflict target
---> ERROR:  unexpected failure to find arbiter index
with insert_cte as (
  insert into test_hyper(metric_id, time, value)
  values (1, NOW(), 0)
  on conflict (metric_id, time) do nothing
)
select 1;

-- Case 2: insert cte without specified conflict target
---> Server crashes!
with insert_cte as (
  insert into test_hyper(metric_id, time, value)
  values (1, NOW(), 0)
  on conflict do nothing
)
select 1;

Specifying constrained columns results in unexpected failure to find arbiter index. Leaving it out for DO NOTHING seems to cause a segmentation fault, query process ends with exit code 11.

[erimatnor]

@jgranstrom thanks for the really useful bug report. Working on the issue now.

[RobAtticus]

Thanks @jgranstrom , this should be fixed in the just released 0.9.1. Let us know if it isn't, otherwise feel free to close.

[jgranstrom]

I just tested with 0.9.1 and the issue is definitely resolved. Thank you!

[saosebastiao]

Hello, currently I am experiencing this exact error with postgres 11 and timescaledb 1.1.1. Is there a chance this fix was missed for different versions?

[gajus]

Same issue.

Server keeps crashing with

unexpected failure to find arbiter index

error.

Here is the actual error/ query:

https://gist.github.com/gajus/fb71ae081c0d6bbc67eace89d101e151#file-index_error-sql-L1

Table schema:

https://gist.github.com/gajus/6a7fb73216052b4cb1b2c2a6f8eff6f7

> SELECT version();
< PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20181011 (Red Hat 8.2.1-4), 64-bit

TimescaleDB v1.1.1.

[mfreed]

Update: A PR to fix some issues with UPSERTs has been merged in https://github.com/timescale/timescaledb/pull/1026 (which is released as part of https://github.com/timescale/timescaledb/releases/tag/1.2.0).

Please let us know if that solves your problem @gajus , as otherwise we’ll try to more closely reproduce your steps and continue working on the issue if separate.

And thanks again for the detailed report (here and on slack).

[mfreed]

I'm going to close out this issue given the above emoji's, but please feel free to re-open if this was in fact not fixed.