Push down modifying CTEs in distributed INSERT ... SELECT
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
To do rollups using INSERT...SELECT, you need to a way of tracking which data has already been processed to avoid rolling it up twice, which is difficult given current limitations of both distributed INSERT...SELECT and INSERT...SELECT via the coordinator.
A common pattern is to the delete the raw data that you are adding to the rollup. That way, the second time you run the command, it will only process new data.
We would want something like the following to be pushed down:
```
CREATE TABLE data (key int);
CREATE TABLE rollup (key int primary key, count int);
SELECT create_distributed_table('data', 'key');
SELECT create_distributed_table('rollup', 'key');
WITH deleted_rows AS (DELETE FROM data RETURNING *)
INSERT INTO rollup (key, count)
SELECT key, count(*) FROM deleted_rows GROUP BY key
ON CONFLICT (key) DO UPDATE SET count = rollup.count + EXCLUDED.count;
```
Contributor guide
Assessment
This issue has not been assessed yet.