citusdata / citusdata/citus

Columnar ON CONFLICT DO NOTHING fails on tables with PK

Open
#6,298 1 comment 1 reaction 0 assignees View on GitHub
bug columnar sqlancer user_reported
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

`ON CONFLICT DO NOTHING` fails on tables that have unique constraints or primary keys. [Columnar readme file](https://github.com/citusdata/citus/blob/main/src/backend/columnar/README.md) implies that it should work.

```sql
CREATE TABLE test_log (
id int NOT NULL,
descr text,
PRIMARY KEY (id))
USING columnar;

INSERT INTO test_log (id, descr)
VALUES (1, 'teststr')
ON CONFLICT
DO NOTHING;
ERROR: XX000: columnar_tuple_insert_speculative not implemented
LOCATION: columnar_tuple_insert_speculative, columnar_tableam.c:766
```

The same error happens when we have a unique constraint instead of a primary key.

If there are no primary keys or unique constraints, the query works. However, PostgreSQL does not detect conflicts in that scenario and `ON CONFLICT DO NOTHING` does not really impact the query in any way.

```sql
CREATE TABLE test_log_without_pk (
id int NOT NULL,
descr text)
USING columnar;

INSERT INTO test_log_without_pk (id, descr)
VALUES (1, 'teststr')
ON CONFLICT
DO NOTHING;

INSERT INTO test_log_without_pk (id, descr)
VALUES (1, 'teststr')
ON CONFLICT
DO NOTHING;

SELECT
*
FROM
test_log_without_pk;
+----+---------+
| id | descr |
+----+---------+
| 1 | teststr |
| 1 | teststr |
+----+---------+
(2 rows)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.