citusdata / citusdata/citus

Distributed insert .. select might incorrectly generate tasks that reference shell table on upsert

Open
#6,826 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

See the (rightmost) subquery in `distributed statement` below:

```sql
CREATE TABLE upsert_test_1
(
unique_col int UNIQUE,
other_col int,
third_col int
);

SELECT create_reference_table('upsert_test_1');

INSERT INTO upsert_test_1 (unique_col, other_col) SELECT unique_col, other_col FROM upsert_test_1 ON CONFLICT (unique_col)
DO UPDATE SET other_col = (SELECT count(*) from upsert_test_1);
DEBUG: distributed statement: INSERT INTO public.upsert_test_1_102008 AS citus_table_alias (unique_col, other_col) SELECT upsert_test_1.unique_col, upsert_test_1.other_col FROM public.upsert_test_1_102008 upsert_test_1 ON CONFLICT(unique_col) DO UPDATE SET other_col = (SELECT count(*) AS count FROM public.upsert_test_1)
ERROR: cannot execute a distributed query from a query on a shard
DETAIL: Executing a distributed query in a function call that may be pushed to a remote node can lead to incorrect results.
HINT: Avoid nesting of distributed queries or use alter user current_user set citus.allow_nested_distributed_execution to on to allow it with possible incorrectness.
CONTEXT: while executing command on localhost:9701
```

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.