citusdata / citusdata/citus

INSERT..SELECT with recursive CTE in router query fails

Open
#3,443 0 comments 0 reactions 0 assignees View on GitHub
bug warm-up
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.