查看Oracle、MySQL、PostGreSQL中的依赖关系

查看Oracle、MySQL、PostGreSQL中的依赖关系

在有些程序员开发习惯中，喜欢为了应用代码的简洁或复用，而在数据库创建一个复杂关连查询的VIEW，甚至是VIEW套VIEW嵌套使用， 这里就有个问题如果上线后如发现依赖的表字段类型或长度不足时，修复一个view依赖的table列时发现在oracle、mysql、postgresql（本篇等同pg）中有不同的表现， 尤其是使用postgresql的用户需要格外注意， 因为pg 不允许直接修改， 学术派的严谨， 子之琼浆,彼之砒霜. 喜忧参半。

Oracle

SQL> CREATE TABLE t (id integer PRIMARY KEY);
Table created.SQL> CREATE VIEW v AS SELECT * FROM t;
View created.SQL> select status from user_objects where object_name='V';
STATUS
-------
VALIDSQL> DROP TABLE T;
Table dropped.SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALIDSQL> CREATE TABLE t (id integer PRIMARY KEY);
Table created.SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALIDSQL> select * from v;
no rows selectedSQL> select status from user_objects where object_name='V';
STATUS
-------
VALIDSQL> alter table t add name varchar2(20);
Table altered.SQL> select status from user_objects where object_name='V';
STATUS
-------
VALIDSQL> desc vName                            Null?    Type------------------------------- -------- ----------------------------1      ID                              NOT NULL NUMBER(38)SQL> desc tName                            Null?    Type------------------------------- -------- ----------------------------1      ID                              NOT NULL NUMBER(38)2      NAME                                     VARCHAR2(20)SQL> @ddl v
PL/SQL procedure successfully completed.DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
--------------------------------------------------------------------------------CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."V" ("ID") ASSELECT "ID" FROM t;SQL> alter table t modify id number(10);
Table altered.SQL> select * from v;
no rows selectedSQL> desc v;Name                            Null?    Type------------------------------- -------- ----------------------------1      ID                              NOT NULL NUMBER(10)SQL> alter table t rename to t100;
Table altered.SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALIDSQL> select * from v;
select * from v*
ERROR at line 1:
ORA-04063: view "SYS.V" has errors

Note:
在oracle中VIEW依赖的TABLE发生DDL后会变为INVALID状态，在下次调用时自动recompile， 如果依赖的表名，列名存在可以正常查询。修改依赖列长度正常修改，在依赖表rename后VIEW无法查询.

查找依赖关系

SQL> desc dba_dependenciesName                            Null?    Type------------------------------- -------- ----------------------------1      OWNER                           NOT NULL VARCHAR2(128)2      NAME                            NOT NULL VARCHAR2(128)3      TYPE                                     VARCHAR2(19)4      REFERENCED_OWNER                         VARCHAR2(128)5      REFERENCED_NAME                          VARCHAR2(128)6      REFERENCED_TYPE                          VARCHAR2(19)7      REFERENCED_LINK_NAME                     VARCHAR2(128)8      DEPENDENCY_TYPE                          VARCHAR2(4)SQL> @dep % v % %
OWNER            DEPENDENT_NAME                 DEPENDENT_TY REF_OWNER        REF_NAME                       REF_TYPE     DEP_
---------------- ------------------------------ ------------ ---------------- ------------------------------ ------------ ----
SYS              V                              VIEW         SYS              T100                           TABLE        HARDSQL> l1  select2     owner                   dep_owner,3     name                    dep_name,4     type                    dep_type,5     referenced_owner        dep_referenced_owner,6     referenced_name         dep_referenced_name,7     referenced_type         dep_referenced_type,8     dependency_type         dep_dependency_type9  --10  --from dba_dependencies where owner like '&1' and referenced_owner like '&2'11  --from dba_dependencies where owner like '&1' and name like '&2'12  --13  from14     dba_dependencies15  where16     lower(owner) like lower('&1')17  and        lower(name) like lower('&2')18  and        lower(referenced_owner) like lower('&3')19* and        lower(referenced_name) like lower('&4')
SQL>

MySQL

MYSQL_root@localhost [anbob]> CREATE TABLE t (id integer PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)MYSQL_root@localhost [anbob]> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.02 sec)MYSQL_root@localhost [INFORMATION_SCHEMA]> select * from tables where table_schema='anbob' and TABLE_TYPE='VIEW' \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: VIEW
1 row in set (0.00 sec)MYSQL_root@localhost [anbob]> DROP TABLE t;
Query OK, 0 rows affected (0.04 sec)MYSQL_root@localhost [anbob]> select * from v;
ERROR 1356 (HY000): View 'anbob.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use themMYSQL_root@localhost [INFORMATION_SCHEMA]> select * from tables where table_schema='anbob' and TABLE_TYPE='VIEW' \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: View 'anbob.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1 row in set, 1 warning (0.00 sec)MYSQL_root@localhost [INFORMATION_SCHEMA]> CREATE TABLE anbob.t (id integer PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)MYSQL_root@localhost [INFORMATION_SCHEMA]> select * from tables where table_schema='anbob' and TABLE_TYPE='VIEW' \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: VIEW
1 row in set (0.00 sec)MYSQL_root@localhost [INFORMATION_SCHEMA]> desc anbob.v-> ;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)MYSQL_root@localhost [INFORMATION_SCHEMA]> alter table anbob.t add name varchar(20);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0MYSQL_root@localhost [INFORMATION_SCHEMA]> select * from tables where table_schema='anbob' and TABLE_TYPE='VIEW' \G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEWENGINE: NULLVERSION: NULLROW_FORMAT: NULLTABLE_ROWS: NULLAVG_ROW_LENGTH: NULLDATA_LENGTH: NULL
MAX_DATA_LENGTH: NULLINDEX_LENGTH: NULLDATA_FREE: NULLAUTO_INCREMENT: NULLCREATE_TIME: 2022-08-17 18:03:22UPDATE_TIME: NULLCHECK_TIME: NULL
TABLE_COLLATION: NULLCHECKSUM: NULLCREATE_OPTIONS: NULLTABLE_COMMENT: VIEW
1 row in set (0.00 sec)MYSQL_root@localhost [INFORMATION_SCHEMA]> alter table anbob.t rename to t100;
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'information_schema'MYSQL_root@localhost [INFORMATION_SCHEMA]> alter table anbob.t rename to anbob.t100;
Query OK, 0 rows affected (0.20 sec)MYSQL_root@localhost [INFORMATION_SCHEMA]> select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_COMMENT from tables where table_schema='anbob' and TABLE_TYPE='VIEW'
and table_comme
*************************** 1. row ***************************TABLE_SCHEMA: anbobTABLE_NAME: vTABLE_TYPE: VIEW
TABLE_COMMENT: View 'anbob.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1 row in set, 2 warnings (0.01 sec)

Note:
在MySQL中和oracle基本一样， 只是没有oracle的dba_object.status 是否invalid表示，而是在INFORMATION_SCHEMA.TABLE_COMMENT有invalid关键字错误。

PostgreSQL

[local]:5432 postgres@anbob=# CREATE TABLE t (id integer PRIMARY KEY);
CREATE TABLE
[local]:5432 postgres@anbob=# CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
[local]:5432 postgres@anbob=# DROP TABLE t;
ERROR:  cannot drop table t because other objects depend on it
DETAIL:  view v depends on table t
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
[local]:5432 postgres@anbob=# alter table t add name varchar(20);
ALTER TABLE
[local]:5432 postgres@anbob=# select * from v;id
----
(0 rows)[local]:5432 postgres@anbob=# \d vView "public.v"Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------id     | integer |           |          |[local]:5432 postgres@anbob=# drop view v;
DROP VIEW
[local]:5432 postgres@anbob=#  CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
[local]:5432 postgres@anbob=# \d vView "public.v"Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------id     | integer               |           |          |name   | character varying(20) |           |          |[local]:5432 postgres@anbob=# alter table t alter column name type varchar(40);
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v depends on column "name"
[local]:5432 postgres@anbob=#[local]:5432 postgres@anbob=# create view vv as select * from v;
CREATE VIEW
[local]:5432 postgres@anbob=# alter table t alter column name type varchar(40);
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v depends on column "name"
[local]:5432 postgres@anbob=# drop view v;
ERROR:  cannot drop view v because other objects depend on it
DETAIL:  view vv depends on view v
HINT:  Use DROP ... CASCADE to drop the dependent objects too.[local]:5432 postgres@anbob=# explain analyze select * from vv where id=1;QUERY PLAN
-----------------------------------------------------------------------------------------------------------Index Scan using t_pkey on t  (cost=0.15..8.17 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)Index Cond: (id = 1)Planning Time: 0.082 msExecution Time: 0.017 ms
(4 rows)[local]:5432 postgres@anbob=# alter table t rename to t100;
ALTER TABLE
[local]:5432 postgres@anbob=# select * from v;id | name
----+------
(0 rows)

Note:
在PG中view对其使用的对象没有直接依赖关系：依赖对象实际上是视图的rewrite rule, 这增加了另一层间接性。 如果对view依赖的表或表列修改或删除时，需要删除view和重建，当然增加列并没有影响 ， 报错中会提示表的直接依赖，但递归包含并没有， 可以想象如果在PG中大量使用view后期维护表时会有一些小麻烦，这也可以简单认为pg中没有失效对象，至少是view。

另外与oracle 和mysql不同的是，pg中依赖的是对象oid, 在table对象rename后，并不会影响view的使用,依赖的是OID ，而不是NAME.

[local]:5432 postgres@anbob=# alter table t100 add address text;
ALTER TABLE
[local]:5432 postgres@anbob=# alter table t100 drop column address;
ALTER TABLEpostgres=# CREATE TABLE t (id integer PRIMARY KEY);
CREATE TABLE
postgres=# insert into t values(1),(2),(3);
INSERT 0 3
postgres=# select * from t;id 
----123
(3 rows)postgres=# CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
postgres=# 
postgres=# select * from v;id 
----123
(3 rows)postgres=# alter table t rename to tt;
ALTER TABLE
postgres=# select * from v;id 
----123
(3 rows)postgres=# 
postgres=# 

检查依赖关系

[local]:5432 postgres@anbob=# \d pg_dependTable "pg_catalog.pg_depend"Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------classid     | oid     |           | not null |objid       | oid     |           | not null |objsubid    | integer |           | not null |refclassid  | oid     |           | not null |refobjid    | oid     |           | not null |refobjsubid | integer |           | not null |deptype     | "char"  |           | not null |

classid存储包含依赖对象的目录表的对象 ID
objid存储依赖对象的ID
objsubid如果依赖项是针对列的，则存储列号
refclassid,refobjid和refobjsubid上面的三列一样，但是描述了依赖所引用的对象
deptype描述依赖的类型

查找表上的直接视图依赖项

[local]:5432 postgres@anbob=# SELECT v.oid::regclass AS view
anbob-# FROM pg_attribute AS a   -- columns for the table
anbob-#    JOIN pg_depend AS d   -- objects that depend on the column
anbob-#       ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid
anbob-#    JOIN pg_rewrite AS r  -- rules depending on the column
anbob-#       ON r.oid = d.objid
anbob-#    JOIN pg_class AS v    -- views for the rules
anbob-#       ON v.oid = r.ev_class
anbob-# WHERE v.relkind = 'v'    -- only interested in views
anbob-#   -- dependency must be a rule depending on a relation
anbob-#   AND d.classid = 'pg_rewrite'::regclass
anbob-#   AND d.refclassid = 'pg_class'::regclass
anbob-#   AND d.deptype = 'n'    -- normal dependency
anbob-#   AND a.attrelid = 't100'::regclass;view
------vv
(2 rows)

查找对表列的直接依赖关系

[local]:5432 postgres@anbob=# SELECT v.oid::regclass AS view
anbob-# FROM pg_attribute AS a   -- columns for the table
anbob-#    JOIN pg_depend AS d   -- objects that depend on the column
anbob-#       ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid
anbob-#    JOIN pg_rewrite AS r  -- rules depending on the column
anbob-#       ON r.oid = d.objid
anbob-#    JOIN pg_class AS v    -- views for the rules
anbob-#       ON v.oid = r.ev_class
anbob-# WHERE v.relkind = 'v'    -- only interested in views
anbob-#   -- dependency must be a rule depending on a relation
anbob-#   AND d.classid = 'pg_rewrite'::regclass
anbob-#   AND d.refclassid = 'pg_class'::regclass
anbob-#   AND d.deptype = 'n'    -- normal dependency
anbob-#   AND a.attrelid = 't100'::regclass
anbob-#   AND a.attname = 'name';view
------v
(1 row)

递归查找所有依赖视图

[local]:5432 postgres@anbob=#  WITH RECURSIVE views AS (-- get the directly depending viewsSELECT v.oid::regclass AS view,v.relkind = 'm' AS is_materialized,1 AS levelFROM pg_depend AS dJOIN pg_rewrite AS rON r.oid = d.objidJOIN pg_class AS vON v.oid = r.ev_classWHERE v.relkind IN ('v', 'm')AND d.classid = 'pg_rewrite'::regclassAND d.refclassid = 'pg_class'::regclassAND d.deptype = 'n'AND d.refobjid = 't100'::regclass
UNION-- add the views that depend on theseSELECT v.oid::regclass,v.relkind = 'm',views.level + 1FROM viewsJOIN pg_depend AS dON d.refobjid = views.viewJOIN pg_rewrite AS r  ON r.oid = d.objidJOIN pg_class AS vON v.oid = r.ev_classWHERE v.relkind IN ('v', 'm')AND d.classid = 'pg_rewrite'::regclassAND d.refclassid = 'pg_class'::regclassAND d.deptype = 'n'AND v.oid <> views.view  -- avoid loop
)
SELECT format('CREATE%s VIEW %s AS%s',CASE WHEN is_materializedTHEN ' MATERIALIZED'ELSE ''END,view,pg_get_viewdef(view))
FROM views
GROUP BY view, is_materialized
ORDER BY max(level);format
----------------------------------CREATE VIEW v AS SELECT t100.id,+t100.name                   +FROM t100;CREATE VIEW vv AS SELECT v.id,  +v.name                      +FROM v;
(2 rows)