Make placement order consistent during writes
- 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
Assessment
This issue has not been assessed yet.