PostgreSQL中的对象依赖管理概述与有用的检查查询第1部分

主要信息

本文介绍了在PostgreSQL中对象依赖的概念及其重要性，探讨了不同类型的依赖关系，并提供了相关查询用于检查对象之间的依赖性。这些依赖关系对于数据库对象的管理和修改至关重要，可以确保对一个对象的更改不会对其他依赖对象造成意外的影响。

在这一系列的博客中，我们将深入探索如何有效管理PostgreSQL中的对象依赖，确保数据完整性，并在进行变更时避免潜在问题。

在PostgreSQL中，对象绑定或依赖涵盖了各种数据库元素之间的关系。这些相互依赖关系在管理和修改数据库对象时至关重要。它们确保了对一个对象的调整不会意外中断其他依赖对象的功能。

例如，当一个视图依赖于一个表时，对表结构的任何更改如修改使用的列、改变数据类型或甚至删除表都会直接影响关联视图的功能。因此，了解这些依赖关系的复杂性对于做出明智的决策和预防潜在问题至关重要。

在本系列博客中，我们将探索PostgreSQL的对象依赖概念，以及它们在有效管理数据库方面的重要性。了解和管理对象依赖关系对于确保数据完整性以及在不引发预期外问题的情况下进行数据库更改至关重要。

在第一篇文章中，我们将全面介绍对象依赖关系，讨论各种类型的依赖关系，并提供帮助您找到依赖关系的查询，以便您可以采取适当的措施。

前提条件

要跟上本文的内容，请完成以下前提条件：

在Amazon Linux 2023上，您可以使用以下命令下载psql命令行工具以运行所有SQL语句：

bashsudo dnf install postgresql15x8664

对象依赖的类型

本节将介绍不同类型的对象依赖关系，并提供示例代码片段。

列

在PostgreSQL中，列依赖可以发生在一个列依赖另一个同一表中的列时。这通常发生在计算或派生列基于其他列的值的情况下。通过建立列依赖关系，PostgreSQL确保每当相关列的值更改时，派生列会自动更新，从而帮助维护表中的数据一致性和准确性。

下面是一个示例。

创建销售表：

sqlCREATE TABLE sales ( saleid SERIAL PRIMARY KEY productname VARCHAR(100) unitprice DECIMAL(10 2) quantity INT totalamount DECIMAL(10 2) GENERATED ALWAYS AS (unitprice quantity) STORED)

在这个例子中，sales表有一个计算列totalamount，它依赖于unitprice和quantity的值。 totalamount列是通过unitprice和quantity的乘积生成的。这是一个典型的使用场景，展示了同一表中一个列的值如何依赖于其他列的值。

GENERATED ALWAYS AS子句用于定义派生列，而STORED关键字确保值会被物理存储，从而允许高效查询。

这只是PostgreSQL中列依赖的一个示例，展示了如何创建依赖于同一表中其他列的计算或派生列。

索引

当在一个表上创建索引时，它会依赖于该表。对表所做的任何更改例如插入、更新或删除行都可能影响该索引。例如，如果向表中插入一行，则索引会更新以包含新行，以便于高效的数据检索。

下面是一个示例：

sqlCREATE TABLE customers ( customerid SERIAL PRIMARY KEY firstname VARCHAR(50) lastname VARCHAR(50) email VARCHAR(100))

CREATE INDEX idxcustomersemail ON customers(email)

在这个例子中，我们创建了一个名为customers的表，包含客户信息的列。然后我们在email列上创建了一个名为idxcustomersemail的索引。该索引依赖于customers表。

现在，假设我们向customers表插入一行：

sqlINSERT INTO customers (firstname lastname email)VALUES (John Doe johndoe@examplecom)

此插入会触发idxcustomersemail索引的更新，确保它包含新行，以便进行高效的基于电子邮件的搜索。

序列

序列是生成一系列唯一值的对象。它通常用于为表生成唯一标识符。当表中的列被定义为序列时，它在列与序列对象之间建立了一种依赖关系。该列依赖于序列在向表中插入新记录时生成下一个唯一值。

下面是一个示例：

sql 创建一个序列CREATE SEQUENCE employeeidseq

创建一个使用该序列的列的表CREATE TABLE employees ( employeeid INTEGER DEFAULT nextval(employeeidseq) PRIMARY KEY firstname VARCHAR(50) lastname VARCHAR(50))

在这个例子中，我们首先使用CREATE SEQUENCE语句创建一个名为employeeidseq的序列。此序列将用于生成employeeid列的唯一值。

接下来，我们创建一个名为employees的表，包含三列：employeeid、firstname和lastname。employeeid列被定义为具有nextval(employeeidseq)的默认值，这意味着在插入新记录时，它将自动从序列中获取下一个值。employeeid列也被声明为表的主键。

用户定义数据类型

用户定义数据类型UDT表示包含单个或多个字段或属性的结构或记录。它允许将相关数据组合在一起形成单一对象。

UDT用于定义表中的列或作为函数的返回类型。当表的列或函数返回UDT时，会在UDT与表或函数之间建立依赖关系。

考虑以下示例：

sqlCREATE TYPE employeeinfo AS ( name VARCHAR address VARCHAR)

CREATE TABLE employeesinfo ( id SERIAL PRIMARY KEY info employeeinfo)

在这个例子中，employeesinfo表依赖于复合数据类型employeeinfo。对employeeinfo类型定义的任何更改都会影响employeesinfo表。

视图

视图是从查询结果生成的虚拟表。它们依赖于用于定义视图的基础表和列。对引用表或列的任何更改都可能影响基于它们构建的视图。视图提供了一种动态且用户友好的方式来与复杂的数据结构互动，使其成为数据库设计的重要组成部分。

考虑以下示例：

sqlCREATE VIEW orderssummary ASSELECT orderid customerid orderdateFROM orders

orderssummary视图依赖于orders表进行服务。对在视图中使用的orders表列进行的任何调整无论是添加或删除列，还是更改列的数据类型都会影响orderssummary视图的功能。

在这个例子中，orderssummary视图从orders表中选择orderid、customerid和orderdate列。因此，对orders表中这些列的任何更改都将反映在orderssummary视图中。

函数和过程

PostgreSQL中的函数可以依赖于表、视图或其他函数。了解这些依赖关系在开发复杂的数据库函数时至关重要，因为它确保了其可靠性和一致性。

考虑以下示例：

sqlCREATE FUNCTION calculatesalary(employeeid INTEGER)RETURNS NUMERICAS DECLARE hourlywage NUMERIC hoursworked NUMERICBEGIN SELECT wage hours INTO hourlywage hoursworked FROM employees WHERE id = employeeid AND date gt (now()1 monthinterval) SELECT hours INTO hoursworked FROM employees WHERE id = employeeid RETURN hourlywage hoursworkedEND LANGUAGE plpgsql

这个名为calculatesalary的函数依赖于名为employees的表。它根据employeeid参数从employees表中检索wage和hours值。然后，该函数通过乘以hourlywage和hoursworked值来计算并返回工资。

这个函数展示了对employees表的依赖，因为它依赖该表的存在以及表中wage和hours列的可用性。对表结构或函数中直接引用的列名或类型的任何更改都可能影响这个函数的功能。

约束

约束如主键或唯一约束在相关列或表上建立依赖关系。例如，表的外键约束直接依赖于另一张表。这确保了引用表中的数据遵循在所引用表中定义的特定规则。约束在强制数据库中的数据完整性方面发挥了关键作用。

考虑以下示例：

sqlCREATE TABLE customers ( customerid SERIAL PRIMARY KEY email VARCHAR(50) UNIQUE)

CREATE TABLE orderstab ( orderid SERIAL PRIMARY KEY customerid INTEGER REFERENCES customers(customerid) orderdate DATE)

在这个例子中，orderstab表通过customerid外键约束依赖于customers表。

触发器

触发器是数据库对象，在特定事件如数据修改发生时自动运行。它们可以依赖于表、视图或其他触发器。当触发器被触发时，它可能执行依赖于数据或数据库结构的操作，创建数据库管理的动态和事件驱动的特性。

考虑以下示例：

sqlCREATE TABLE products ( productid SERIAL PRIMARY KEY name VARCHAR(50) quantity INTEGER totalsales NUMERIC)

CREATE OR REPLACE FUNCTION updatetotalsales() RETURNS TRIGGER AS BEGIN NEWtotalsales = NEWquantity 10 假设每单位的价格是10 RETURN NEWEND LANGUAGE plpgsql

CREATE TRIGGER productstriggerBEFORE INSERT OR UPDATE ON productsFOR EACH ROWEXECUTE FUNCTION updatetotalsales()

在这个例子中，触发器函数updatetotalsales()将数量乘以10假设每单位价格为10并更新totalsales列。触发器productstrigger在对products表执行INSERT或UPDATE操作前运行该函数。

检查和分析对象依赖关系

在PostgreSQL中，可以确定哪些对象如表、视图或函数相互依赖。此信息存储在PostgreSQL系统目录中，一个包含关于数据库中所有对象的元数据的一组表和视图。通过使用适当的SQL查询这些系统目录视图，您可以获得关于对象依赖关系的见解。这些依赖关系在对数据库对象进行更改时非常重要，因为更改可能对依赖对象造成连锁影响。因此，理解这些依赖关系可以帮助您维护数据库的完整性和功能。

以下是PostgreSQL提供的一些基本目录视图：

通过这些工具，您可以查看数据库的内部工作原理并了解不同部分如何链接。这样的知识有助于您在管理PostgreSQL数据库时做出明智的决策，几乎就像是拼图中的侦探，找出所有部分是如何契合的。

查询以查找对象依赖关系

本节将向您展示用于查找每种对象类型的依赖关系的查询。

查找外键依赖关系

要查找与引用完整性约束引用相关的表依赖关系，请使用以下查询：

sqlSELECT laattrelidregclass AS referencingtable laattname AS referencingcolumnFROM pgcatalogpgconstraint AS c JOIN pgcatalogpgindex AS i ON iindexrelid = cconindid JOIN pgcatalogpgattribute AS la ON laattrelid = cconrelid AND laattnum = cconkey[1] JOIN pgcatalogpgattribute AS ra ON raattrelid = cconfrelid AND raattnum = cconfkey[1]WHERE cconfrelid = lttablenamegtregclass AND ccontype = f AND cardinality(cconfkey) = 1

根据之前的示例，orders和orderitems表是依赖的。orderitems表的orderid列依赖于orders表的orderid列。现在让我们运行针对orders表的查询，查看依赖的表：

sqlSELECT laattrelidregclass AS referencingtable laattname AS referencingcolumn FROM pgcatalogpgconstraint AS c JOIN pgcatalogpgindex AS i ON iindexrelid = cconindid JOIN pgcatalogpgattribute AS la ON laattrelid = cconrelid AND laattnum = cconkey[1] JOIN pgcatalogpgattribute AS ra ON raattrelid = cconfrelid AND raattnum = cconfkey[1] WHERE cconfrelid = ordersregclass AND ccontype = f AND cardinality(cconfkey) = 1

以下是查询的输出：

plaintext referencingtable referencingcolumn orderitems orderid(1 row)

查询显示orderitems表和orderid列为所创建表中的依赖。

查找表类型依赖关系

以下查询用于查找表的依赖表类型：

sqlSELECT attrelidregclass AS sourcetable atttypidregtype AS dependenttabletypeFROM pgcatalogpgattributeWHERE attrelid = ltsourcetablegtregclass AND atttypidregtypetext IN ( SELECT relname FROM pgcatalogpgclass)

在我们的其他示例中，car表依赖于person表，因为car表中某一列的数据类型是person表。

sqlSELECT attrelidregclass AS sourcetable atttypidregtype AS dependenttabletype FROM pgcatalogpgattribute WHERE attrelid = carregclass AND atttypidregtypetext IN ( SELECT relname FROM pgcatalogpgclass)

以下是查询的输出：

plaintext sourcetable dependenttabletype car person(1 row)

查找某列对另一列的依赖关系

以下查询用于查找同一表中某一列的依赖列：

sqlSELECT DISTINCT nnspname AS tableschema crelname AS tablename acattname AS columnname adattname AS dependentcolumn FROM pgcatalogpgnamespace n pgcatalogpgclass c pgcatalogpgdepend d pgcatalogpgattribute ac pgcatalogpgattribute ad pgcatalogpgattrdef atd WHERE noid = crelnamespace AND coid = acattrelid AND coid = adattrelid AND acattnum ltgt adattnum AND adattrelid = atdadrelid AND adattnum = atdadnum AND dclassid = pgattrdefregclassoid AND drefclassid = pgclass