Columnar ON CONFLICT DO NOTHING fails on tables with PK
- 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
Assessment
This issue has not been assessed yet.