The c77_dep extension provides a comprehensive suite of tools for analyzing, visualizing, and managing database object dependencies in PostgreSQL databases. It helps database administrators and developers understand complex dependency relationships between tables, views, materialized views, and foreign tables.

Overview

Managing dependencies in complex PostgreSQL databases can be challenging. When making schema changes, it's crucial to understand how objects are interconnected to prevent breaking changes. This extension provides views and functions to map these relationships and make informed decisions during database maintenance, migrations, and refactoring.

Features

Dependency Mapping: Core views that analyze and expose database object dependencies
Impact Analysis: Tools to predict the impact of dropping objects or changing schemas
Circular Dependency Detection: Identification of problematic circular dependencies
Risk Assessment: Scoring of objects based on their dependency characteristics
Migration Planning: Functions to help determine the correct order for migrations
Visualization: Export dependency graphs in DOT format for Graphviz visualization
Reporting: Generate comprehensive dependency analysis reports

Installation

Prerequisites

PostgreSQL 10 or later

Installing the Extension

Copy the extension files to your PostgreSQL extension directory:

cp c77_dep.control c77_dep--1.0.0.sql /path/to/postgresql/share/extension/

Connect to your database and create the extension:
```
CREATE EXTENSION c77_dep;
```

Usage

Core Views

c77_dep_dependencies_map: Base dependency mapping view for tables, views, and materialized views
c77_dep_dependencies_map_with_foreign: Enhanced view that also includes foreign tables

Analysis Views

c77_dep_orphaned_objects: Objects with no dependencies
c77_dep_schema_complexity: Schema complexity metrics
c77_dep_object_type_summary: Summary of object types and dependencies
c77_dep_risk_assessment: Risk assessment for schema changes
c77_dep_hub_objects: Objects with many dependencies
c77_dep_cleanup_candidates: Objects that might need cleanup

Analysis Functions

c77_dep_analyze_drop_impact(object_name): Analyze the impact of dropping an object
c77_dep_detect_circular_dependencies(): Detect circular dependencies
c77_dep_export_dependency_graph([schema], [max_depth], [include_foreign]): Export dependencies in DOT format
c77_dep_simulate_schema_change(schema_name, [new_schema_name]): Simulate schema change impact
c77_dep_generate_migration_order(): Generate suggested migration order
c77_dep_generate_report([schema], [include_orphans], [include_circular]): Generate comprehensive report

Examples

Finding orphaned objects in a schema

SELECT * FROM c77_dep_orphaned_objects 
WHERE relation LIKE 'myschema.%';

Analyzing the impact of dropping a table

SELECT * FROM c77_dep_analyze_drop_impact('myschema.mytable');

Generating a dependency graph for visualization

SELECT c77_dep_export_dependency_graph('myschema');

Save the output to a file with a .dot extension and visualize it with Graphviz:

dot -Tpng dependencies.dot -o dependencies.png

Generate a comprehensive dependency report

SELECT c77_dep_generate_report();

Find circular dependencies

SELECT * FROM c77_dep_detect_circular_dependencies();

Determine migration order for a schema

SELECT * FROM c77_dep_generate_migration_order()
WHERE objects_to_migrate @> ARRAY['myschema.%'];

Performance Considerations

The dependency analysis can be resource-intensive on large databases. Consider:

Running analyses during off-peak hours
Filtering by schema to limit analysis scope
Using the functions that target specific objects rather than analyzing the entire database

Limitations

The extension focuses primarily on structural dependencies and may not capture all logical dependencies
Function dependencies are not included to avoid excessive resource usage
Very large databases may require schema-specific analysis rather than database-wide analysis

Version History

1.0.0: Initial release with core dependency mapping views and analysis tools

Contributing

Contributions are welcome! Please feel free to submit pull requests or open issues.

License

This extension is available under the PostgreSQL License.

Acknowledgments

This extension would not exist without the wealth of information available in PostgreSQL's system catalogs and the excellent documentation on dependency tracking in PostgreSQL.

Created by [trogers1884]