Feature introduction | detailed explanation of dependency constraints of PostgreSQL – system table PG_ depend & pg_ constraint

Time:2021-10-14

This article was first published on 15:28:08, November 4, 2015

preface

This article was written earlier and relies on PostgreSQL version 9.3. Subsequent kernel versions may be incompatible, but the core principles are interlinked and can be used as a reference.

Table structure

pg_depend

pg_ Depend is a system table of Postgres, which is used to record the dependencies between database objects. In addition to the common main foreign keys, there are other internal dependencies that can be presented through this system table.

postgres=# \d+ pg_depend
                       Table "pg_catalog.pg_depend"
   Column    |  Type   | Modifiers | Storage | Stats target | Description
-------------+---------+-----------+---------+--------------+-------------
 CLassID | oid | not null | plain | system oid
 Objid | oid | not null | plain | object oid
 objsubid    | integer | not null  | plain   |              |
 Refclassid | oid | not null | plain | reference system oid
 Refobjid | oid | not null | plain | reference object ID
 refobjsubid | integer | not null  | plain   |              |
 deptype     | "char"  | not null  | plain   |              | pg_ Dependent type
Indexes:
    "pg_depend_depender_index" btree (classid, objid, objsubid)
    "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
Has OIDs: no

Oid is the abbreviation of object identifier, which means object ID. because it is an unsigned 4-byte type, the range is not large enough, so it is generally not used as a primary key, but only used in internal systems, such as system tables. Can be converted with some integer numbers. The relevant system parameters aredefault_with_oids, the default is off.

pg_depend.deptypeThe field has an extension type since version 9.1. The current types are:

DEPENDENCY_NORMAL (n): common dependent objects, such as the relationship between tables and schemas.
DEPENDENCY_AUTO (a): automatic dependent objects, such as primary key constraints.
DEPENDENCY_INTERNAL (i): an internal dependent object, usually the object itself.
DEPENDENCY_EXTENSION (e): 9.1 new extension dependency.
DEPENDENCY_PIN (p): system built-in dependencies.

pg_constraint

postgres=# \d pg_constraint
     Table "pg_catalog.pg_constraint"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 Conname | name | not null -- constraint name
 Connamespace | oid | not null -- oid of the namespace where the constraint is located
 Contype | char | not null -- constraint type
 Conddeferrable | Boolean | not null -- whether the constraint can be postponed
 Conddeferred | Boolean | not null -- by default, whether constraints can be deferred
 Validated | Boolean | not null -- whether the constraint has been validated
 Conrelid | oid | not null -- oid of the table where the constraint is located
 Contypid | oid | not null -- oid of the domain where the constraint is located
 Conindid | oid | not null -- if it is a unique, primary key, foreign key or exclusion constraint, it is the index supporting this constraint; Otherwise, 0
 Confirid | oid | not null -- if it is a foreign key, it is the reference table; Otherwise, 0
 Confupdtype | "char" | not null -- foreign key update operation code
 Confideltype | char | not null -- foreign key deletion operation code
 Confimatchtype | char | not null -- foreign key matching type
 conislocal    | boolean      | not null        
 Coninhcount | integer | not null -- constrains the number of ancestors inherited directly
 connoinherit  | boolean      | not null        
 Conkey | smallint [] | -- if it is a table constraint (including foreign keys but not constraint triggers), it is a list of constraint fields
 Conf key | smallint [] | -- if it is a foreign key, it is the list of referenced fields
 Conpfeqop | oid [] | -- if it is a foreign key, it is a list of equality operators for PK = FK comparison
 Conppeqop | oid [] | -- if it is a foreign key, it is a list of equality operators for PK = PK comparison
 Confifeqop | oid [] | -- if it is a foreign key, it is a list of equality operators for FK = FK comparison
 Conexclop | oid [] | -- if it is an exclusion constraint, it is a list of exclusion operators for each field
 conbin        | pg_ node_ Tree | -- if it is a check constraint, it is the internal form of its expression
 Consrc | text | -- if it is a check constraint, it is the human readable form of the expression
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)

SQL for querying dependencies

The following SQL can list various dependencies of system and user objects:

SELECT classid::regclass AS "depender object class",
    CASE classid
        WHEN 'pg_class'::regclass THEN objid::regclass::text
        WHEN 'pg_type'::regclass THEN objid::regtype::text
        WHEN 'pg_proc'::regclass THEN objid::regprocedure::text
        ELSE objid::text
    END AS "depender object identity",
    objsubid,
    refclassid::regclass AS "referenced object class",
    CASE refclassid
        WHEN 'pg_class'::regclass THEN refobjid::regclass::text
        WHEN 'pg_type'::regclass THEN refobjid::regtype::text
        WHEN 'pg_proc'::regclass THEN refobjid::regprocedure::text
        ELSE refobjid::text
    END AS "referenced object identity",
    refobjsubid,
    CASE deptype
        WHEN 'p' THEN 'pinned'
        WHEN 'i' THEN 'internal'
        WHEN 'a' THEN 'automatic'
        WHEN 'n' THEN 'normal'
    END AS "dependency type"
FROM pg_catalog.pg_depend WHERE (objid >= 16384 OR refobjid >= 16384);

I usually like to add a condition after whereand deptype <>'i'To exclude internal dependencies.

Example

Create a table:

postgres=# create table tbl_parent(id int);
CREATE TABLE

SQL to execute query dependencies:

Postgres = # execute the above SQL;
 depender object class | depender object identity | objsubid | referenced object class | referenced object identity | refobjsubid | dependency type
-----------------------+--------------------------+----------+-------------------------+------------- pg_class              | tbl_parent               |        0 | pg_namespace            | 2200                       |           0 | normal
(1 row)

It seems that only a table is built without constraints. In fact, the table is built under the schema, so it only depends on the schema.

Add primary key constraint:

postgres=# alter table tbl_parent add primary key(id);
ALTER TABLE
 depender object class | depender object identity | objsubid | referenced object class | referenced object identity | refobjsubid | dependency type
-----------------------+--------------------------+----------+-------------------------+------- pg_class              | tbl_parent               |        0 | pg_namespace            | 2200                       |           0 | normal
 pg_constraint         | 16469                    |        0 | pg_class                | tbl_parent                 |           1 | automatic
(2 rows)

Constraint type changed toautomatic, indicating that the primary key constraint depends on the table and is in automatic mode. The details can be found in the system tablepg_constrantCheck inside.

Under normal circumstances, when you delete a dependent object, you will be prompted to delete the dependent object first. However, if the dependent objects are deleted through the system table, an exception will be caused if the operation is wrong. For example, the following operations will result in an errorcache lookup failed for constraint

postgres=# select oid,conname,connamespace,contype from pg_constraint where conname like 'tbl_parent%';
  oid  |     conname     | connamespace | contype
-------+-----------------+--------------+---------
 16469 | tbl_parent_pkey |         2200 | p
(1 row)
 
postgres=# delete from pg_constraint where conname like 'tbl_parent%';
DELETE 1
postgres=# select oid,conname,connamespace,contype from pg_constraint where conname like 'tbl_parent%';
 oid | conname | connamespace | contype
-----+---------+--------------+---------
(0 rows)
 
postgres=# drop table tbl_parent;
Error: cache lookup failed for constraint 16469 -- 16496 is the oid of the constraint
postgres=#

This error occurs because the constraint object is manually deleted, but in PG_ However, dependencies still exist in depend. Therefore, when deleting the table, an error is reported because the innermost dependency object cannot be found.


Welcome to my WeChat public official account: sharing the mainstream open source database and storage engine related technologies.

Feature introduction | detailed explanation of dependency constraints of PostgreSQL - system table PG_ depend & pg_ constraint

title website
GitHub https://dbkernel.github.io
Know https://www.zhihu.com/people/…
Segmentfault https://segmentfault.com/u/db…
Nuggets https://juejin.im/user/5e9d3e…
Open source China https://my.oschina.net/dbkernel
Cnblogs https://www.cnblogs.com/dbkernel

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]