138 lines
4.8 KiB
Markdown
138 lines
4.8 KiB
Markdown
# c77_dep - PostgreSQL Database Dependency Mapping Extension
|
|
|
|
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
|
|
|
|
1. Copy the extension files to your PostgreSQL extension directory:
|
|
```bash
|
|
cp c77_dep.control c77_dep--1.0.0.sql /path/to/postgresql/share/extension/
|
|
```
|
|
|
|
2. Connect to your database and create the extension:
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
SELECT * FROM c77_dep_orphaned_objects
|
|
WHERE relation LIKE 'myschema.%';
|
|
```
|
|
|
|
### Analyzing the impact of dropping a table
|
|
|
|
```sql
|
|
SELECT * FROM c77_dep_analyze_drop_impact('myschema.mytable');
|
|
```
|
|
|
|
### Generating a dependency graph for visualization
|
|
|
|
```sql
|
|
SELECT c77_dep_export_dependency_graph('myschema');
|
|
```
|
|
Save the output to a file with a `.dot` extension and visualize it with Graphviz:
|
|
```bash
|
|
dot -Tpng dependencies.dot -o dependencies.png
|
|
```
|
|
|
|
### Generate a comprehensive dependency report
|
|
|
|
```sql
|
|
SELECT c77_dep_generate_report();
|
|
```
|
|
|
|
### Find circular dependencies
|
|
|
|
```sql
|
|
SELECT * FROM c77_dep_detect_circular_dependencies();
|
|
```
|
|
|
|
### Determine migration order for a schema
|
|
|
|
```sql
|
|
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:
|
|
|
|
1. Running analyses during off-peak hours
|
|
2. Filtering by schema to limit analysis scope
|
|
3. 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] |