citusdata / citusdata/citus

Unexpected "missing FROM-clause entry for view" error when deleting from a view that has a DELETE rule

Open
#5,491 0 comments 0 reactions 0 assignees View on GitHub
bug dev_reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.