citusdata / citusdata/citus

Intra-node isolation anomaly

Open
#7,366 2 comments 0 reactions 0 assignees View on GitHub
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

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.