Distributed insert .. select might incorrectly generate tasks that reference shell table on upsert
- 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
Assessment
This issue has not been assessed yet.