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時，
例如：表的某一列normal depend其資料類型。
- 删除A不會影響B的使用。
- 級聯删除B時也會删除A。
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 時，
例如：用來保證外鍵限制的觸發器 internal depend on 特定限制。
- 依賴對象作為關聯對象的一部分而建立。
- 依賴對象是不可以删除的，推薦使用删除關聯對象代替。
- 不管是否指定CASCADE，删除關聯對象時衍生删除依賴對象。
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
![Mysql表結構轉Postgresql建表語句快速操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)