PostgreSQL中的依赖类型

最新推荐文章于 2024-05-10 02:10:16 发布

constzl

最新推荐文章于 2024-05-10 02:10:16 发布

阅读量2.1k

点赞数

分类专栏： PostgreSQL 文章标签： PostgreSQL Dependency AntDB

本文链接：https://blog.csdn.net/constzl/article/details/79716456

版权

PostgreSQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

PostgreSQL中的依赖类型

pg_depend

postgres=# \d+ pg_depend
                       Table "pg_catalog.pg_depend"
   Column    |  Type   | Modifiers | Storage | Stats target | Description 
-------------+---------+-----------+---------+--------------+-------------
 classid     | oid     | not null  | plain   |              | 
 objid       | oid     | not null  | plain   |              | 
 objsubid    | integer | not null  | plain   |              | 
 refclassid  | oid     | not null  | plain   |              | 
 refobjid    | oid     | not null  | plain   |              | 
 refobjsubid | integer | not null  | plain   |              | 
 deptype     | "char"  | not null  | plain   |              | 
Indexes:
    "pg_depend_depender_index" btree (classid, objid, objsubid)
    "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)

DependencyType

typedef enum DependencyType
{
    DEPENDENCY_NORMAL = 'n',
    DEPENDENCY_AUTO = 'a',
    DEPENDENCY_INTERNAL = 'i',
    DEPENDENCY_EXTENSION = 'e',
    DEPENDENCY_AUTO_EXTENSION = 'x',
    DEPENDENCY_PIN = 'p'
} DependencyType;

通过 pg_depend 中的 deptype 字段，指示 objects 之间的依赖关系。下面分别记录不同的依赖类型有何区别，适用什么情况下。

DEPENDENCY_NORMAL (‘n’)

DEPENDENCY_NORMAL (‘n’): normal relationship between separately-created objects. The dependent object may be dropped without affecting the referenced object. The referenced object may only be dropped by specifying CASCADE, in which case the dependent object is dropped too. Example: a table column has a normal dependency on its datatype.

A normal depend on B时，

删除A不会影响B的使用。
级联删除B时也会删除A。

例如：表的某一列normal depend其数据类型。

postgres=# CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');
CREATE TYPE
Time: 394.890 ms
postgres=# CREATE TABLE enumtest (col rainbow, id serial) distribute by hash(id);
CREATE TABLE
Time: 503.425 ms
postgres=# \d+ enumtest
                                             Table "public.enumtest"
 Column |  Type   |                       Modifiers                       | Storage | Stats target | Description 
--------+---------+-------------------------------------------------------+---------+--------------+-------------
 col    | rainbow |                                                       | plain   |              | 
 id     | integer | not null default nextval('enumtest_id_seq'::regclass) | plain   |              | 
Distribute By: HASH(id)
Location Nodes: ALL DATANODES

postgres=# select * from pg_depend where objid = 'enumtest'::regclass and objsubid = 1;               
 classid | objid  | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------+--------+----------+------------+----------+-------------+---------
    1259 | 122911 |        1 |       1247 |   122897 |           0 | n
(1 row)

Time: 2.949 ms
postgres=# drop type rainbow ;
ERROR:  cannot drop type rainbow because other objects depend on it
DETAIL:  table enumtest column col depends on type rainbow
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Time: 1.926 ms
postgres=# drop table enumtest;
DROP TABLE
Time: 207.980 ms
postgres=# CREATE TABLE enumtest (col rainbow, id serial) distribute by hash(id);
CREATE TABLE
Time: 289.175 ms
postgres=# drop type rainbow cascade;
NOTICE:  drop cascades to table enumtest column col
DROP TYPE
Time: 23.841 ms
postgres=# \d+ enumtest
                                             Table "public.enumtest"
 Column |  Type   |                       Modifiers                       | Storage | Stats target | Description 
--------+---------+-------------------------------------------------------+---------+--------------+-------------
 id     | integer | not null default nextval('enumtest_id_seq'::regclass) | plain   |              | 
Distribute By: HASH(id)
Location Nodes: ALL DATANODES

DEPENDENCY_AUTO (‘a’)

DEPENDENCY_AUTO (‘a’): the dependent object can be dropped separately from the referenced object, and should be automatically dropped (regardless of RESTRICT or CASCADE mode) if the referenced object is dropped. Example: a named constraint on a table is made auto-dependent on the table, so that it will go away if the table is dropped.

A auto depend on B时，

删除A不影响B的使用。
删除B时自动删除A（无论RESTRICT或CASCADE）。

例如：表的某一个约束自动依赖表，当表删除时，约束自动删除。

postgres=# create table m(id int, value int, constraint pk_m_id primary key(id));
CREATE TABLE
Time: 219.317 ms
postgres=# select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype from pg_depend where refobjid = 'm'::regclass;
    classid    | objid  | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------------+--------+----------+------------+----------+-------------+---------
 pg_type       | 122942 |        0 | pg_class   |   122940 |           0 | i
 pgxc_class    | 122940 |        0 | pg_class   |   122940 |           0 | i
 pg_constraint | 122944 |        0 | pg_class   |   122940 |           1 | a
(3 rows)

Time: 3.209 ms
postgres=# alter table m drop CONSTRAINT pk_m_id;
ALTER TABLE
Time: 1232.094 ms
postgres=# select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype from pg_depend where refobjid = 'm'::regclass;
  classid   | objid  | objsubid | refclassid | refobjid | refobjsubid | deptype 
------------+--------+----------+------------+----------+-------------+---------
 pg_type    | 122942 |        0 | pg_class   |   122940 |           0 | i
 pgxc_class | 122940 |        0 | pg_class   |   122940 |           0 | i
(2 rows)

Time: 3.329 ms
postgres=# alter table m add CONSTRAINT pk_m_id primary key(id);
ALTER TABLE
Time: 84.707 ms
postgres=# select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype from pg_depend where refobjid = 'm'::regclass;
    classid    | objid  | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------------+--------+----------+------------+----------+-------------+---------
 pg_type       | 122942 |        0 | pg_class   |   122940 |           0 | i
 pgxc_class    | 122940 |        0 | pg_class   |   122940 |           0 | i
 pg_constraint | 122946 |        0 | pg_class   |   122940 |           1 | a
(3 rows)

Time: 3.270 ms

DEPENDENCY_INTERNAL (‘i’)

DEPENDENCY_INTERNAL (‘i’): the dependent object was created as part of creation of the referenced object, and is really just a part of its internal implementation. A DROP of the dependent object will be disallowed outright (we’ll tell the user to issue a DROP against the referenced object, instead). A DROP of the referenced object will be propagated through to drop the dependent object whether CASCADE is specified or not. Example: a trigger that’s created to enforce a foreign-key constraint is made internally dependent on the constraint’s pg_constraint entry.

A internal depend B 时，

依赖对象作为关联对象的一部分而创建。
依赖对象是不可以删除的，推荐使用删除关联对象代替。
不管是否指定CASCADE，删除关联对象时衍生删除依赖对象。

例如：用来保证外键约束的触发器 internal depend on 特定约束。

postgres=# create table testa(id serial primary key, sex varchar(2)) DISTRIBUTE BY REPLICATION;
CREATE TABLE
Time: 252.203 ms
postgres=# insert into testa(sex) values('m');
INSERT 0 1
Time: 19.538 ms
postgres=# create table test(id serial primary key, name varchar(20), sid integer references testa on delete cascade) DISTRIBUTE BY REPLICATION;
CREATE TABLE
Time: 256024.945 ms
postgres=# \d+ test
                                                    Table "public.test"
 Column |         Type          |                     Modifiers                     | Storage  | Stats target | Description 
--------+-----------------------+---------------------------------------------------+----------+--------------+-------------
 id     | integer               | not null default nextval('test_id_seq'::regclass) | plain    |              | 
 name   | character varying(20) |                                                   | extended |              | 
 sid    | integer               |                                                   | plain    |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "test_sid_fkey" FOREIGN KEY (sid) REFERENCES testa(id) ON DELETE CASCADE
Distribute By: REPLICATION
Location Nodes: ALL DATANODES

postgres=# select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype from pg_depend where refobjid = (select oid from pg_constraint where conname = 'test_sid_fkey');
  classid   | objid  | objsubid |  refclassid   | refobjid | refobjsubid | deptype 
------------+--------+----------+---------------+----------+-------------+---------
 pg_trigger | 131120 |        0 | pg_constraint |   131119 |           0 | i
 pg_trigger | 131121 |        0 | pg_constraint |   131119 |           0 | i
 pg_trigger | 131122 |        0 | pg_constraint |   131119 |           0 | i
 pg_trigger | 131123 |        0 | pg_constraint |   131119 |           0 | i
(4 rows)

Time: 3.512 ms

DEPENDENCY_EXTENSION (‘e’)

DEPENDENCY_EXTENSION (‘e’): the dependent object is a member of the extension that is the referenced object. The dependent object can be dropped only via DROP EXTENSION on the referenced object. Functionally this dependency type acts the same as an internal dependency, but it’s kept separate for clarity and to simplify pg_dump.

A extension depend on B 时，与 DEPENDENCY_INTERNAL 类似，依赖对象作为扩展的一部分而依赖扩展（关联对象）。

postgres=# select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype from pg_depend where deptype = 'e';                     
   classid   | objid | objsubid |  refclassid  | refobjid | refobjsubid | deptype 
-------------+-------+----------+--------------+----------+-------------+---------
 pg_proc     | 13454 |        0 | pg_extension |    13453 |           0 | e
 pg_proc     | 13455 |        0 | pg_extension |    13453 |           0 | e
 pg_proc     | 13456 |        0 | pg_extension |    13453 |           0 | e
 pg_language | 13457 |        0 | pg_extension |    13453 |           0 | e
(4 rows)

Time: 3.428 ms
postgres=# select proname from pg_proc where oid in (13454, 13455, 13456);
        proname         
------------------------
 plpgsql_call_handler
 plpgsql_inline_handler
 plpgsql_validator
(3 rows)

Time: 1.387 ms
postgres=# select * from pg_language where oid = 13457;
 lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
---------+----------+---------+--------------+---------------+-----------+--------------+--------
 plpgsql |       10 | t       | t            |         13454 |     13455 |        13456 | 
(1 row)

Time: 1.520 ms

DEPENDENCY_AUTO_EXTENSION (‘x’)

DEPENDENCY_AUTO_EXTENSION (‘x’): the dependent object is not a member of the extension that is the referenced object (and so should not be ignored by pg_dump), but cannot function without the extension and should be dropped when the extension itself is. The dependent object may be dropped on its own as well.

依赖对象不是扩展（关联对象）的一部分，但离了扩展（关联对象）不起作用，关联对象删除时依赖对象也删除。依赖对象也可以独自删除。

没找到类似的用法��

DEPENDENCY_PIN (‘p’)

DEPENDENCY_PIN (‘p’): there is no dependent object; this type of entry is a signal that the system itself depends on the referenced object, and so that object must never be deleted. Entries of this type are created only during initdb. The fields for the dependent object contain zeroes.

pin dependency 用于标记系统自身依赖关联对象，不允许删除，没有实质的依赖对象。只在initdb时创建（函数 setup_depend）。

postgres=# select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype from pg_depend where deptype = 'p' limit 10;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------+-------+----------+------------+----------+-------------+---------
 -       |     0 |        0 | pg_class   |     1247 |           0 | p
 -       |     0 |        0 | pg_class   |     1249 |           0 | p
 -       |     0 |        0 | pg_class   |     1255 |           0 | p
 -       |     0 |        0 | pg_class   |     1259 |           0 | p
 -       |     0 |        0 | pg_class   |     2604 |           0 | p
 -       |     0 |        0 | pg_class   |     2606 |           0 | p
 -       |     0 |        0 | pg_class   |     2611 |           0 | p
 -       |     0 |        0 | pg_class   |     2610 |           0 | p
 -       |     0 |        0 | pg_class   |     2617 |           0 | p
 -       |     0 |        0 | pg_class   |     2753 |           0 | p
(10 rows)

Time: 1.396 ms

constzl

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL中的依赖类型

PostgreSQL中的依赖类型pg_dependpostgres=# \d+ pg_depend Table "pg_catalog.pg_depend" Column | Type | Modifiers | Storage | Stats target | Description -------------+-...
复制链接

扫一扫