GBase 8c Object Dependency Checks Before Schema Changes

The real challenge in a gbase database schema change isn't writing the DDL — it's uncovering every object that depends on the table before you hit “execute.” Many production incidents are caused not by syntax errors, but by overlooked views, triggers, or functions that silently break. This article uses PG_DEPEND, PG_REWRITE, PG_TRIGGER, and other system catalogs to build a structured pre‑change inspection routine.

Classifying Risk by Change Type

DDL Type	Key Risk	Recommended Approach
Add column	Default values / NOT NULL can force a full table rewrite	Add nullable column first, backfill in batches, then add constraints
Drop column	Dependent objects may break; space is not reclaimed immediately	Audit dependencies before physical removal
Alter column type	Likely rewrites the entire table — heavy window, 2× disk usage	Use a “new column” strategy for hot tables
Rename table / column	Easy to miss application SQL, stored procedures	Scan database objects, then application code
Drop table	Maximum blast radius; `CASCADE` misuse is hard to undo	List all dependents explicitly

Before any change, answer two questions: Does this rewrite the whole table? and How many objects are attached upstream and downstream?

Core System Catalog Queries

Catalog / View	Purpose
`PG_TABLES`	Shows trigger existence, creation and last DDL time
`PG_OBJECT`	Object creator and modification timestamps
`PG_DEPEND`	Dependency map; `deptype` is critical
`PG_REWRITE`	Traces view/rule references back to base tables
`PG_VIEWS`	View definitions
`PG_TRIGGER`	Trigger details, function, enabled status
`PG_PROC`	Function / procedure source code and arguments

1. Check Object State and Recent Changes

SELECT schemaname, tablename, tableowner,
       hasindexes, hasrules, hastriggers,
       created, last_ddl_time
FROM pg_tables
WHERE schemaname = 'acct' AND tablename = 'txn_order';

Add more detail with PG_OBJECT:

SELECT object_oid, object_type, creator,
       ctime, mtime, changecsn
FROM pg_object
WHERE object_oid = 'acct.txn_order'::regclass;

Pay close attention to last_ddl_time and mtime — many surprises come from recent, undocumented modifications.

2. View Dependencies via PG_REWRITE

A simple text search on PG_VIEWS is not enough. Combine PG_DEPEND with PG_REWRITE to find every view that relies on the table.

SELECT n.nspname      AS dep_schema,
       c.relname      AS dep_view,
       r.rulename     AS rule_name,
       d.deptype
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class c   ON c.oid = r.ev_class
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE d.classid    = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.refobjid   = 'acct.txn_order'::regclass
ORDER BY 1, 2;

If this query returns rows, avoid blunt operations like dropping a column or changing its type directly.

3. Triggers and Stored Procedures

Find all triggers on the table:

SELECT n.nspname        AS schema_name,
       c.relname        AS table_name,
       t.tgname         AS trigger_name,
       p.proname        AS trigger_func,
       t.tgenabled,
       t.tgisinternal
FROM pg_trigger t
JOIN pg_class c     ON c.oid = t.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_proc p      ON p.oid = t.tgfoid
WHERE n.nspname = 'acct' AND c.relname = 'txn_order'
ORDER BY t.tgname;

Search function/procedure source code for references:

SELECT n.nspname, p.proname, p.prokind,
       p.prosrc, p.proargsrc
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosrc ILIKE '%txn_order%'
   OR COALESCE(p.proargsrc, '') ILIKE '%txn_order%'
ORDER BY 1, 2;

Remember: dynamically constructed SQL inside procedures won't appear here — application code must be audited separately.

4. Understanding deptype to Control Cascade Risk

The deptype column in pg_depend is your safety switch:

deptype	Meaning	Strategy
`n`	Normal dependency	Requires `CASCADE`; must be manually confirmed
`a`	Automatic dependency	Removed with the parent
`i`	Internal dependency	Do not touch directly
`p`	Pinned system dependency	Off‑limits

Treat every n dependency as a mandatory review item. Never experiment with i or p in production.

Safer Alternative for Column Type Changes

For large tables, replacing a column is often safer than altering its type directly.

-- 1. Add the new column
ALTER TABLE acct.txn_order ADD COLUMN settle_ts_new timestamp;

-- 2. Backfill in batches
UPDATE acct.txn_order
   SET settle_ts_new = to_timestamp(settle_ts_old, 'YYYY-MM-DD HH24:MI:SS')
 WHERE settle_ts_old IS NOT NULL AND settle_ts_new IS NULL;

-- 3. Update dependent views to use the new column
CREATE OR REPLACE VIEW rpt.v_order_day AS
SELECT order_id, settle_ts_new AS settle_ts, amount, status_code
FROM acct.txn_order;

-- 4. After observation, drop the old column (optional)

This breaks a high‑risk atomic change into observable, reversible steps.

Pre‑Change Audit Script

Log the dependency landscape right before the change window:

gsql -d finance_core -h 192.0.2.31 -p 5432 -U app_dba <<'SQL'
\o ddl_precheck_txn_order_20260402.log
SELECT now();

SELECT schemaname, tablename, hastriggers, created, last_ddl_time
FROM pg_tables
WHERE schemaname = 'acct' AND tablename = 'txn_order';

SELECT n.nspname, c.relname, r.rulename, d.deptype
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class c   ON c.oid = r.ev_class
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE d.classid    = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.refobjid   = 'acct.txn_order'::regclass
ORDER BY 1,2;

SELECT n.nspname, c.relname, t.tgname, p.proname, t.tgenabled
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_proc p ON p.oid = t.tgfoid
WHERE n.nspname = 'acct' AND c.relname = 'txn_order'
ORDER BY t.tgname;
\o
SQL

Common Pitfalls

Pitfall	Correct Approach
Only checking view names	Always inspect `PG_VIEWS.definition`
Ignoring triggers	Add table‑level trigger checks to every DDL review
Assuming `DROP COLUMN` frees space instantly	Space reclamation is a separate operation
Using `CASCADE` casually	Export the dependent object list before cascading
Checking only database objects	Scan application SQL, ETL jobs, and report scripts as well

Summary

A table schema change is really a reorganization of object relationships. In GBase 8c, combining PG_DEPEND, PG_REWRITE, and PG_TRIGGER with a “dependencies first, cost assessment second, implementation path last” workflow catches most production surprises before they happen.

For any gbase database environment, making this pre‑check a habit turns risky DDL from a leap of faith into a controlled, predictable procedure.