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:  ms
postgres=# CREATE TABLE enumtest (col rainbow, id serial) distribute by hash(id);
CREATE TABLE
Time:  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 = ;               
 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:  ms
postgres=# drop table enumtest;
DROP TABLE
Time:  ms
postgres=# CREATE TABLE enumtest (col rainbow, id serial) distribute by hash(id);
CREATE TABLE
Time:  ms
postgres=# drop type rainbow cascade;
NOTICE:  drop cascades to table enumtest column col
DROP TYPE
Time:  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:  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:  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:  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()) DISTRIBUTE BY REPLICATION;
CREATE TABLE
Time:  ms
postgres=# insert into testa(sex) values('m');
INSERT  
Time:  ms
postgres=# create table test(id serial primary key, name varchar(), sid integer references testa on delete cascade) DISTRIBUTE BY REPLICATION;
CREATE TABLE
Time:  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() |                                                   | 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 (, , );
        proname         
------------------------
 plpgsql_call_handler
 plpgsql_inline_handler
 plpgsql_validator
(3 rows)

Time: 1.387 ms
postgres=# select * from pg_language where oid = ;
 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 ;
 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

PostgreSQL中的依赖类型

PostgreSQL中的依赖类型

pg_depend

DependencyType

DEPENDENCY_NORMAL (‘n’)

DEPENDENCY_AUTO (‘a’)

DEPENDENCY_INTERNAL (‘i’)

DEPENDENCY_EXTENSION (‘e’)

DEPENDENCY_AUTO_EXTENSION (‘x’)

DEPENDENCY_PIN (‘p’)

继续阅读

SQL语句实现数据分页

Mysql表结构转Postgresql建表语句快速操作

postgis学习笔记

postgis 常用 sql 20210707

postgres 性能优化小技巧Performance Tips1) Using EXPLAIN2) Statistics Used by the Planner3) Controlling the Planner with Explicit JOIN Clauses4) Populating a Database5) Non-Durable Settings

使用eclipse 搭建 spring boot + mybatis 连接 postgresql数据库

oracle sql 多表嵌套子查询连接查询， join where exist in 的区别

C#连接postgresql数据库

Maven深入学习（四）- 知识总结

关于 java 转码 GBK -> UTF-8

postgresql替换换行符

查询数据库中存在的回车换行符

Django入门：第一个Django应用

postgresql 关于GBK和utf-8字符集问题

PostgreSQL t_bits计算方法

为什么要选择UniDAC