[Bug]: Incorrect explain output for INSERT ... ON CONFLICT queries on a hypertable

[lkshminarayanan]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Query executor

What happened?

Running EXPLAIN ANALYZE INSERT ... ON CONFLICT statements on a hypertable reports wrong number of Conflicting Tuples and Tuples Inserted when there is a conflict.

TimescaleDB version affected

2.11.2

PostgreSQL version used

15.4

What operating system did you use?

Ubuntu

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

postgres=# -- Create the table
CREATE TABLE devices (
  device_id int NOT NULL,
  time timestamptz NOT NULL,
  value float
);
CREATE TABLE
Time: 17.413 ms
postgres=# 
postgres=# -- Unique index on device_id, time
CREATE UNIQUE INDEX uidx ON devices(device_id, time desc);
CREATE INDEX
Time: 11.397 ms
postgres=# 
postgres=# -- Create hypertable
SELECT create_hypertable('devices', 'time');
  create_hypertable   
----------------------
 (1,public,devices,t)
(1 row)

Time: 15.292 ms
postgres=# 
postgres=# 
postgres=# -- Insert a row
INSERT INTO devices (device_id, time, value)
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:34', 1000);
INSERT 0 1
Time: 22.266 ms
postgres=# 
postgres=# 
postgres=# 
postgres=# -- Explain an insert of the same row again with ON CONFLICT
EXPLAIN ANALYZE INSERT INTO devices (device_id, time, value)
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:34', 1000)
  ON CONFLICT DO NOTHING;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Custom Scan (HypertableModify)  (cost=0.00..0.01 rows=1 width=20) (actual time=0.607..0.616 rows=0 loops=1)
   ->  Insert on devices  (cost=0.00..0.01 rows=1 width=20) (actual time=0.607..0.616 rows=0 loops=1)
         Conflict Resolution: NOTHING
         Tuples Inserted: 1
         Conflicting Tuples: 0
         ->  Custom Scan (ChunkDispatch)  (cost=0.00..0.01 rows=1 width=20) (actual time=0.558..0.568 rows=1 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=20) (actual time=0.008..0.011 rows=1 loops=1)
 Planning Time: 0.114 ms
 Execution Time: 0.680 ms
(9 rows)

Time: 10.441 ms
postgres=#

Note that despite the conflict, the explain reports Tuples Inserted as 1 and Conflicting Tuples as 0. Without EXPLAIN, the query works as expected - the issue is only on the EXPLAIN.

How can we reproduce the bug?

-- Create the table
CREATE TABLE devices (
  device_id int NOT NULL,
  time timestamptz NOT NULL,
  value float
);

-- Unique index on device_id, time
CREATE UNIQUE INDEX uidx ON devices(device_id, time desc);

-- Create hypertable
SELECT create_hypertable('devices', 'time');

-- Insert a row
INSERT INTO devices (device_id, time, value)
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:34', 1000);

-- Explain an insert of the same row again with ON CONFLICT
EXPLAIN ANALYZE INSERT INTO devices (device_id, time, value)
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:34', 1000)
  ON CONFLICT DO NOTHING;