Allow UPDATE/DELETE reference tables selecting from distributed tables
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Citus currently doesn't allow UPDATE/DELETE reference tables selecting from distributed tables. See the steps to reproduce and the workaound
```SQL
CREATE TABLE reference_table (key int);
SELECT create_reference_table('reference_table');
CREATE TABLE distributed_table (key int);
SELECT create_distributed_table('distributed_table', 'key');
-- a known limitation
UPDATE reference_table SET key = distributed_table.key FROM distributed_table;
ERROR: only reference tables may be queried when targeting a reference table with multi shard UPDATE/DELETE queries with multiple tables
DELETE FROM reference_table USING distributed_table WHERE reference_table.key = distributed_table.key;
ERROR: only reference tables may be queried when targeting a reference table with multi shard UPDATE/DELETE queries with multiple tables
-- workaround is to wrap the distributed table (or distributed table JOINs) to a CTE
WITH distributed_table AS (SELECT key FROM distributed_table)
UPDATE reference_table SET key = distributed_table.key FROM distributed_table;
UPDATE 0
WITH distributed_table AS (SELECT key FROM distributed_table)
DELETE FROM reference_table USING distributed_table WHERE reference_table.key = distributed_table.key;
DELETE 0
```
Contributor guide
Assessment
This issue has not been assessed yet.