View dependency query produces false positives and misses view-to-view dependencies

[da77a]

View dependency query produces false positives and misses view-to-view dependencies

pg-schema-diff version

v1.0.5

Problem

The GetViews query in internal/queries/queries.sql has two issues in its table_dependencies subquery:

1. Missing view-to-view dependencies

The join filter dep_c.relkind IN ('r', 'p') excludes views ('v'). Views that depend on other views (e.g. CREATE VIEW a AS SELECT * FROM other_view) have no dependency edge, so they can be ordered before the view they reference.

Fix: Change to dep_c.relkind IN ('r', 'p', 'v') and add dep_c.oid != c.oid to exclude self-references (every view's rewrite rule has a dependency on itself).

2. False-positive dependencies from other views' rewrite rules

The join path:

FROM pg_catalog.pg_depend AS d
INNER JOIN pg_catalog.pg_rewrite AS r ON d.objid = r.oid
INNER JOIN pg_catalog.pg_depend AS d2 ON r.oid = d2.objid
...
WHERE d.refobjid = c.oid

This finds rewrite rules that reference the current view (d.refobjid = c.oid), then finds what those rules depend on. But OTHER views' rewrite rules also reference this view (if they SELECT from it). This causes view A to appear to depend on view B's dependencies, creating false cycles.

Fix: Add AND r.ev_class = c.oid to the pg_rewrite join to restrict to the view's own rewrite rule only.

Reproducer

CREATE TABLE t1 (id int PRIMARY KEY, val text);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM v1;  -- v2 depends on v1

Running pg-schema-diff plan --from-dsn empty --to-dsn target:

• Without fix: v2 may be ordered before v1 (missing dep), or cycle detected (false positive from other rules)
• With fix: correct order t1 → v1 → v2

Related

• [BUG] Functions with knowable dependencies are created before the tables they depend on #248 (function composite type ordering — different issue but same dependency-tracking gap)