INSERT INTO SELECT between 2 colocated tables with rep_factor 2 and with primary key violation may show wrong result
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Unfortunately I don't have the exact steps for reproducing the issue. Below are **the pseudo steps** of how this happened in a customer cluster. The source table had close to 4.5 billion rows.
**Source Table Setup**
```
CREATE TABLE test(id serial, tenant_id int, primary key (id,tenant_id));
SELECT create_distributed_table('test','tenant_id');
--Load data.
SET citus.shard_replication_factor to 2;
SELECT replicate_table_shards('test');
```
**Destination Table Setup**
```
--Creating a new table with replication_factor setting 1 is not respected i.e it's replication_factor is 2 in real
show citus.shard_replication_factor ;
citus.shard_replication_factor
--------------------------------
1
(1 row)
CREATE TABLE dest(id serial, tenant_id int, primary key (id,tenant_id));
SELECT create_distributed_table('dest','tenant_id');
SELECT count(*),shardid from pg_dist_shard_placement JOIN pg_dist_shard USING (shardid) where logicalrelid='dest'::regclass group by shardid;
count | shardid
-------+---------
2 | 102136
2 | 102137
2 | 102138
2 | 102139
2 | 102140
2 | 102141
2 | 102142
2 | 102143
...
...
SELECT colocationid,logicalrelid from pg_dist_partition;
colocationid | logicalrelid
--------------+-----------------
1 | test
1 |dest
```
**INSERT INTO SELECT shows wrong results**
```
INSERT INTO dest SELECT * from test;
WARNING: (1233,2) violates pkey constraint etc etc.
DETAIL: etc etcc.
WARNING: (1233,2) violates pkey constraint etc etc.
DETAIL: etc etcc.
WARNING: (1233,2) violates pkey constraint etc etc.
DETAIL: etc etcc.
SELECT count(*) from test;
count
----
4.6 billion
SELECT count(*) from dest;
count
---
4.4 billion
```
Instead of WARNING we should have errored out.
Im not sure why this issue happened, it could also be because of 'id' serial column in the destination table. And Im not sure whether the id column in the source table was populated.
Contributor guide
Assessment
This issue has not been assessed yet.