Do not allow accesses to citus local tables with triggers on mx workers in a xact block
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
We execute the triggers on the shard placements of citus local tables instead of the shell table.
This ends-up being 2PC inside another 2PC case and errors out in MX as below:
```sql
CREATE OR REPLACE FUNCTION insert_42() RETURNS trigger AS $insert_42$
BEGIN
INSERT INTO distributed_table VALUES (42) ON CONFLICT (d) DO UPDATE SET a = distributed_table .a + 1;
RETURN NEW;
END;
$insert_42$ LANGUAGE plpgsql;
CREATE TRIGGER insert_42_trigger
AFTER DELETE ON citus_local_table
FOR EACH ROW EXECUTE FUNCTION insert_42();
-- fails on the worker as expected
BEGIN;
DELETE FROM citus_local_table WHERE l1 IN (2,3,4);
COMMIT;
ERROR: cannot use 2PC in transactions involving multiple servers
CONTEXT: while executing command on localhost:5432
```
We should at least detect this case beforehand and give a better error.
Also see https://github.com/citusdata/citus/pull/3995#pullrequestreview-462451466 / 3
Contributor guide
Assessment
This issue has not been assessed yet.