citusdata / citusdata/citus

Make placement order consistent during writes

Open
#3,061 7 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

While making citus work with pg12 we ran into an issue that is suspected to be caused by an inconsistent placement order for tables with `shard_replication_factor` > 1.

Failing test suite `with_transaction` in non-adaptive mode.
Query:
```sql
-- multi-statement multi shard modifying statements should work
BEGIN;
SELECT count (*) FROM second_raw_table;
count
-------
101
(1 row)

WITH distinct_count AS (
SELECT count(DISTINCT created_at) FROM raw_table
),
ids_inserted AS
(
INSERT INTO raw_table VALUES (11, 1000, now()) RETURNING tenant_id
)
UPDATE raw_table SET created_at = '2001-02-10 20:00:00'
WHERE tenant_id IN (SELECT tenant_id FROM ids_inserted) AND tenant_id < (SELECT count FROM distinct_count);
DEBUG: generating subplan 9_1 for CTE distinct_count: SELECT count(DISTINCT created_at) AS count FROM with_transactions.raw_table
DEBUG: generating subplan 9_2 for CTE ids_inserted: INSERT INTO with_transactions.raw_table (tenant_id, income, created_at) VALUES (11, 1000, now()) RETURNING tenant_id
DEBUG: Plan 9 query after replacing subqueries and CTEs: UPDATE with_transactions.raw_table SET created_at = 'Sat Feb 10 20:00:00 2001 PST'::timestamp with time zone WHERE ((tenant_id OPERATOR(pg_catalog.=) ANY (SELECT ids_inserted.tenant_id FROM (SELECT intermediate_result.tenant_id FROM read_intermediate_result('9_2'::text, 'binary'::citus_copy_format) intermediate_result(tenant_id integer)) ids_inserted)) AND (tenant_id OPERATOR(pg_catalog.<) (SELECT distinct_count.count FROM (SELECT intermediate_result.count FROM read_intermediate_result('9_1'::text, 'binary'::citus_copy_format) intermediate_result(count bigint)) distinct_count)))
+ERROR: cannot establish a new connection for placement 800007, since DML has been executed on a connection that is in use
TRUNCATE second_raw_table;
+ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT;
```

For now we have punted on this issue by going back to `replication_factor = 1` during this test.

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.