lkshminarayanan commented 1 year ago

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Compression

What happened?

When two INSERT INTO ... ON CONFLICT DO NOTHING queries are run on the same compressed tuple in parallel sessions, one of them ends up throws the following error :

ERROR: duplicate key value violates unique constraint "_hyper_1_1_chunk_uidx" DETAIL: Key (device_id, "time")=(1, 2000-01-06 03:30:00+05:30) already exists.

It should not throw this error as the INSERT query uses ON CONFLICT DO NOTHING.

TimescaleDB version affected

2.10.1

PostgreSQL version used

15.2

What operating system did you use?

Ubuntu 22.04

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

Begin INSERT INTO in Session 1 :

postgres=# BEGIN;
BEGIN
Time: 0.756 ms
postgres=*# 
postgres=*# INSERT INTO devices
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:30', 1000) 
  ON CONFLICT (device_id, time) DO NOTHING;
INSERT 0 0
Time: 3.567 ms

Begin INSERT INTO in Session 2 and observe it waiting for the lock :

postgres=# BEGIN;
BEGIN
Time: 5.597 ms
postgres=*# 
postgres=*# INSERT INTO devices
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:30', 1000) 
  ON CONFLICT (device_id, time) DO NOTHING;
<waits for lock from Session 1>

Commit Session 1:

postgres=*# COMMIT;
COMMIT
Time: 10.745 ms

Session 2 throws an error:

postgres=*# INSERT INTO devices
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:30', 1000) 
  ON CONFLICT (device_id, time) DO NOTHING;
ERROR:  duplicate key value violates unique constraint "_hyper_1_1_chunk_uidx"
DETAIL:  Key (device_id, "time")=(1, 2000-01-02 03:30:00+05:30) already exists.
Time: 5186.492 ms (00:05.186)

How can we reproduce the bug?

Session 1 - Setup Tables and BEGIN TRANSACTION

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

-- Create unique index on device_id, time
CREATE UNIQUE INDEX uidx ON devices(device_id, time);

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

-- Populate rows
INSERT INTO devices
  SELECT id, time, ceil(random() * 100)::int
    FROM generate_series(1,5,1) id,
           generate_series('2000-01-01 0:00:00+0'::timestamptz,
                           '2000-01-05 23:55:00+0','2h') time;

-- Compress the table
ALTER TABLE devices SET (timescaledb.compress);
SELECT compress_chunk(i, if_not_compressed => true)
  FROM show_chunks('devices') i;
SELECT compression_status FROM chunk_compression_stats('devices');

-- Begin transaction
BEGIN;
INSERT INTO devices
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:30', 1000)
    ON CONFLICT (device_id, time) DO NOTHING;

Session 2 - Execute the parallel INSERT INTO DML.

-- This query will wait for locks from Session1 to release
INSERT INTO devices
  VALUES (1, TIMESTAMP '2000-01-01 05:30:00+05:30', 1000)
    ON CONFLICT (device_id, time) DO NOTHING;

Commit Session 1

COMMIT;

After the commit in Session1, Session 2 will fail with the above mentioned error.

antekresic commented 1 year ago

This happens because constraint checking makes us decompress the compressed segment into the uncompressed chunk twice at the same time and we hit the unique constraint violation.