Intra-node isolation anomaly
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Simple ACID-test (sum calculation under pgbench), PostgreSQL 15.1/Citus 12.1
1. Prepare data:
```sql
CREATE TABLE table_a
( id bigint not null
, shard_n int not null
, value int not null default 0
, primary key(shard_n, id)
);
SELECT create_distributed_table('table_a', 'shard_n', shard_count := 2);
-- even to shard #1, odd to shard #2
INSERT INTO table_a (id, shard_n)
SELECT i, 2 - i % 2 FROM generate_series(1, 1024) c(i);
SELECT shard_name, nodename, nodeport, citus_table_type FROM citus_shards;
-- shard_name | nodename | nodeport | citus_table_type
-- ----------------+-----------+----------+------------------
-- table_a_102008 | 127.0.0.1 | 5433 | distributed
-- table_a_102009 | 127.0.0.1 | 5433 | distributed
```
2. Run pgbench:
```sh
pgbench -c 4 -T 100 -h 127.0.0.1 -p 5433 -f update.sql
```
update.sql:
```sql
\set id random(1, 512)
BEGIN;
-- update #1: random row from table_a_102008 (node 1)
UPDATE table_a SET value = value + 1 WHERE id = (2 * :id - 1) and shard_n = 1;
-- update #2: random row from table_a_102009 (node 1)
UPDATE table_a SET value = value - 1 WHERE id = 2 * :id and shard_n = 2;
COMMIT;
```
3. Check sum under pgbench (should always be 0) - **always failed**
```sql
SELECT sum(value) FROM table_a;
```
Contributor guide
Assessment
This issue has not been assessed yet.