Intra-node isolation anomaly about citus HOT 2 OPEN

The problem is lack of distributed snapshot isolation. A basic solution is proposed in #6489

from citus.

pgbench -c 4 -T 100 -h 127.0.0.1 -p 5433 -f update.sql

I think I know the problem here. You distribute the table against the column 'shard_n'. (row goes to node1 if it is 1, to node2 if it is 2)

Assumption is that we increment a row and decrement another in each transaction, but this is wrong. id check in WHERE clause could cause no row is updated. Not all ids (random generated) exist in each node.

To fix the problem, you can change the insert query like below (to make sure all id in range (1,1024) exists in both shards):

INSERT INTO table_a (id, shard_n)
SELECT i, 1 FROM generate_series(1, 1024) c(i);

INSERT INTO table_a (id, shard_n)
SELECT i, 2 FROM generate_series(1, 1024) c(i);

from citus.