首页 » MySQL, ORACLE 9i-23c, PostgreSQL/GaussDB » VIEW dependencies in Oracle、MySQL、PostGreSQL(数据库比较系列十一)

VIEW dependencies in 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

检查依赖关系

[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)

[local]:5432 postgres@anbob=#

— over —

reference TRACKING VIEW DEPENDENCIES IN POSTGRESQL

打赏

对不起,这篇文章暂时关闭评论。