citusdata / citusdata/citus

Allow UPDATE/DELETE reference tables selecting from distributed tables

Open
#4,475 0 comments 0 reactions 0 assignees View on GitHub
sql
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

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.