查看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
-------
VALID
SQL> DROP TABLE T;
Table dropped.
SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALID
SQL> CREATE TABLE t (id integer PRIMARY KEY);
Table created.
SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALID
SQL> select * from v;
no rows selected
SQL> select status from user_objects where object_name='V';
STATUS
-------
VALID
SQL> alter table t add name varchar2(20);
Table altered.
SQL> select status from user_objects where object_name='V';
STATUS
-------
VALID
SQL> desc v
Name Null? Type
------------------------------- -------- ----------------------------
1 ID NOT NULL NUMBER(38)
SQL> desc t
Name 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") AS
SELECT "ID" FROM t;
SQL> alter table t modify id number(10);
Table altered.
SQL> select * from v;
no rows selected
SQL> 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
-------
INVALID
SQL> 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_dependencies
Name 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 HARD
SQL> l
1 select
2 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_type
9 --
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 from
14 dba_dependencies
15 where
16 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: def
TABLE_SCHEMA: anbob
TABLE_NAME: v
TABLE_TYPE: VIEW
ENGINE: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: NULL
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-08-17 18:03:22
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: NULL
CHECKSUM: NULL
CREATE_OPTIONS: NULL
TABLE_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 them
MYSQL_root@localhost [INFORMATION_SCHEMA]> select * from tables where table_schema='anbob' and TABLE_TYPE='VIEW' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: anbob
TABLE_NAME: v
TABLE_TYPE: VIEW
ENGINE: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: NULL
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-08-17 18:03:22
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: NULL
CHECKSUM: NULL
CREATE_OPTIONS: NULL
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, 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: def
TABLE_SCHEMA: anbob
TABLE_NAME: v
TABLE_TYPE: VIEW
ENGINE: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: NULL
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-08-17 18:03:22
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: NULL
CHECKSUM: NULL
CREATE_OPTIONS: NULL
TABLE_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: 0
MYSQL_root@localhost [INFORMATION_SCHEMA]> select * from tables where table_schema='anbob' and TABLE_TYPE='VIEW' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: anbob
TABLE_NAME: v
TABLE_TYPE: VIEW
ENGINE: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: NULL
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-08-17 18:03:22
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: NULL
CHECKSUM: NULL
CREATE_OPTIONS: NULL
TABLE_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: anbob
TABLE_NAME: v
TABLE_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 v
View "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 v
View "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 ms
Execution 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 TABLE
postgres=# 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
----
1
2
3
(3 rows)
postgres=# CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
postgres=#
postgres=# select * from v;
id
----
1
2
3
(3 rows)
postgres=# alter table t rename to tt;
ALTER TABLE
postgres=# select * from v;
id
----
1
2
3
(3 rows)
postgres=#
postgres=#
检查依赖关系
[local]:5432 postgres@anbob=# \d pg_depend
Table "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
------
v
v
(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 views
SELECT v.oid::regclass AS view,
v.relkind = 'm' AS is_materialized,
1 AS level
FROM pg_depend AS d
JOIN pg_rewrite AS r
ON r.oid = d.objid
JOIN pg_class AS v
ON v.oid = r.ev_class
WHERE v.relkind IN ('v', 'm')
AND d.classid = 'pg_rewrite'::regclass
AND d.refclassid = 'pg_class'::regclass
AND d.deptype = 'n'
AND d.refobjid = 't100'::regclass
UNION
-- add the views that depend on these
SELECT v.oid::regclass,
v.relkind = 'm',
views.level + 1
FROM views
JOIN pg_depend AS d
ON d.refobjid = views.view
JOIN pg_rewrite AS r
ON r.oid = d.objid
JOIN pg_class AS v
ON v.oid = r.ev_class
WHERE v.relkind IN ('v', 'm')
AND d.classid = 'pg_rewrite'::regclass
AND d.refclassid = 'pg_class'::regclass
AND d.deptype = 'n'
AND v.oid <> views.view -- avoid loop
)
SELECT format('CREATE%s VIEW %s AS%s',
CASE WHEN is_materialized
THEN ' 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)
