INSERT..SELECT with recursive CTE in router query fails
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
The following router query works:
```sql
postgres=# WITH RECURSIVE a (n) AS (SELECT x FROM test WHERE x = 1 UNION ALL SELECT n+1 FROM a WHERE n < 10) SELECT n, n FROM a;
┌────┬────┐
│ n │ n │
├────┼────┤
│ 1 │ 1 │
│ 2 │ 2 │
│ 3 │ 3 │
│ 4 │ 4 │
│ 5 │ 5 │
│ 6 │ 6 │
│ 7 │ 7 │
│ 8 │ 8 │
│ 9 │ 9 │
│ 10 │ 10 │
└────┴────┘
(10 rows)
```
However, the same query fails if the result is inserted into a distributed table:
```sql
NOTICE: issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 15, '2020-01-30 02:04:23.127167+01');
DETAIL: on server marco@localhost:9701 connectionId: 1
NOTICE: issuing WITH a(n) AS (SELECT test.x FROM public.test_102009 test WHERE (test.x OPERATOR(pg_catalog.=) 1) UNION ALL SELECT (a.n OPERATOR(pg_catalog.+) 1) FROM a WHERE (a.n OPERATOR(pg_catalog.<) 10)) SELECT n AS x, n_1 AS y FROM (SELECT a.n, a.n FROM a) citus_insert_select_subquery(n, n_1)
DETAIL: on server marco@localhost:9701 connectionId: 1
NOTICE: issuing ROLLBACK
DETAIL: on server marco@localhost:9701 connectionId: 1
ERROR: relation "a" does not exist
DETAIL: There is a WITH item named "a", but it cannot be referenced from this part of the query.
HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
CONTEXT: while executing command on localhost:9701
```
The reason is that the RECURSIVE keyword is not included in the CTE that is sent to the worker.
Contributor guide
Research direction
Start by reproducing the recursive CTE INSERT..SELECT against a distributed table and compare the router query with the CTE sent to the worker. Trace the router's worker-query generation, then verify that the generated CTE retains the RECURSIVE keyword and that the insert succeeds without the relation "a" error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 45/100