Unexpected "missing FROM-clause entry for view" error when deleting from a view that has a DELETE rule
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
This happens if view contains a set operation targeting multiple tables, at least one of which is a citus table.
```sql
CREATE TABLE dist_table_1 (id int, test text);
SELECT create_distributed_table('dist_table_1', 'id');
CREATE TABLE dist_table_2 (id int, test text);
SELECT create_distributed_table('dist_table_2', 'id');
CREATE OR REPLACE VIEW view_on_dist AS
SELECT id, test
FROM dist_table_1
UNION
SELECT id, test
FROM dist_table_2
;
CREATE OR REPLACE RULE delete_rule AS
ON DELETE TO view_on_dist DO INSTEAD (
DELETE FROM dist_table_1
WHERE id = OLD.id;
DELETE FROM dist_table_2
WHERE id = OLD.id;
);
```
```sql
DELETE FROM view_on_dist;
....
NOTICE: issuing DELETE FROM public.dist_table_1_102008 dist_table_1 WHERE (dist_table_1.id OPERATOR(pg_catalog.=) view_on_dist.id)
....
ERROR: missing FROM-clause entry for table "view_on_dist"
```
This seems to be because, we're missing to add an rte for the view when building the query.
Contributor guide
Assessment
This issue has not been assessed yet.