Solve: How to Trace Recursive View Dependencies in Postgres with Python

Solve: How to Trace Recursive View Dependencies in Postgres with Python


Sometimes your queries tell the truth too loudly. That was the case when Prasad spotted the same view name appearing as both the source and the destination in his dependency results. It looked like a mistake—until you realize Postgres is just doing what it does best: being honest about its internal wiring.


This kind of result usually means one of two things. First, you're likely looking at a self-referencing view or a recursive common table expression (CTE). Postgres treats those honestly, and the catalog tables reflect it. Second, you might be dealing with duplicate names or alias confusion. If your view names aren't fully qualified by schema, or if aliases are reused in a layered query, the dependency tracker can create strange echoes.

So how do you tell what’s really happening? You could manually dig through pg_depend, pg_rewrite, and pg_class, filtering out obvious self-references with a WHERE source.oid <> target.oid clause. That might give you a cleaner output—but it won't help you see the bigger picture. To understand the real relationships between views, especially when they're layered several levels deep, you need a way to walk the dependency tree.

Python Script One: Basic Recursive Dependency Walker

Here’s a small but powerful Python script that connects to Postgres, builds a view dependency graph, and recursively prints a tree showing which views depend on which. If there's a circular dependency, it flags it clearly. The script uses psycopg2, so you'll need to install that if you haven't already:

Bash

pip install psycopg2   

Then download the Python script from this GitHub Gist, and either run it directly or paste it into your own Python file or notebook:

Python

import psycopg2

def get_view_dependencies(conn):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT 
                dependent.relname AS dependent_view,
                source.relname AS source_view
            FROM 
                pg_rewrite r
            JOIN 
                pg_class dependent ON r.ev_class = dependent.oid
            JOIN 
                pg_depend d ON d.objid = r.oid
            JOIN 
                pg_class source ON d.refobjid = source.oid
            WHERE 
                dependent.relkind = 'v' AND 
                source.relkind = 'v';
        """)
        rows = cur.fetchall()

    graph = {}
    for dep, src in rows:
        graph.setdefault(src, []).append(dep)
    return graph

def walk_tree(graph, view, visited=None, indent=0):
    if visited is None:
        visited = set()
    if view in visited:
        print("  " * indent + f"{view} (cycle)")
        return
    visited.add(view)
    print("  " * indent + view)
    for child in graph.get(view, []):
        walk_tree(graph, child, visited.copy(), indent + 1)

# Example usage:
conn = psycopg2.connect("dbname=yourdb user=youruser password=yourpass host=yourhost")
graph = get_view_dependencies(conn)
walk_tree(graph, "your_root_view")   

You can start from any view in your system by changing the last line. The result will be a clean tree-like printout:

Bash

view_a
  view_b
    view_c
  view_d (cycle)

Coming Soon: Python Script Two - Interactive CLI with Linux Tree Output

We’re building a second version of this tool as a proper command-line utility. It will:

• Prompt for database connection or use env vars
• Ask for the root view name
• Let you choose between a basic indented style or a Linux-style tree
• Include exit/quit functionality and optional command-line args

Perfect for cloud and data engineers who want quick, readable output without opening pgAdmin or writing custom SQL each time. The interactive mode will feel like a built-in Linux tool.

Stay tuned for that CLI version in the next entry of the Solve series. And remember: when Postgres echoes the same name twice, it isn’t confused. It’s just showing you something recursive.

More soon in the Solve and Insight series.

Need Postgres Expertise?

We're happy to help you with your Postgres projects!  Feel free to contact us.

Email: info@pacificw.com

Image: Gemini