PostgreSQL教程-数据定义-使用继承实现声明式分区

时间：2021-11-18 来源：

5.11.3. 使用继承实现

虽然内建的声明式分区适合于大部分常见的用例，但还是有一些场景需要更加灵活的方法。分区可以使用表继承来实现，这能够带来一些声明式分区不支持的特性，例如：

对声明式分区来说，分区必须具有和分区表正好相同的列集合，而在表继承中，子表可以有父表中没有出现过的额外列。

表继承允许多继承。

声明式分区仅支持范围、列表以及哈希分区，而表继承允许数据按照用户的选择来划分(不过注意，如果约束排除不能有效地剪枝子表，查询性能可能会很差)。

在使用声明式分区时，一些操作比使用表继承时要求更长的持锁时间。例如，向分区表中增加分区或者从分区表移除分区要求在父表上取得一个ACCESS EXCLUSIVE锁，而在常规继承的情况下一个SHARE UPDATE EXCLUSIVE锁就足够了。

5.11.3.1. 例子

我们使用上面用过的同一个measurement表。为了使用继承实现分区，可使用下面的步骤：

创建“主”表，所有的“子”表都将从它继承。这个表将不包含数据。不要在这个表上定义任何检查约束，除非想让它们应用到所有的子表上。同样，在这个表上定义索引或者唯一约束也没有意义。对于我们的例子来说，主表是最初定义的measurement表。

创建数个“子”表，每一个都从主表继承。通常，这些表将不会在从主表继承的列集合之外增加任何列。正如声明性分区那样，这些表就是普通的PostgreSQL表(或者外部表)。

CREATE TABLE measurement_y2006m02 () INHERITS (measurement);

CREATE TABLE measurement_y2006m03 () INHERITS (measurement);

...

CREATE TABLE measurement_y2007m11 () INHERITS (measurement);

CREATE TABLE measurement_y2007m12 () INHERITS (measurement);

CREATE TABLE measurement_y2008m01 () INHERITS (measurement);

为子表增加不重叠的表约束来定义每个分区允许的键值。

典型的例子是：

CHECK ( x = 1 )

CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))

CHECK ( outletID >= 100 AND outletID < 200 )

确保约束能保证不同子表允许的键值之间没有重叠。设置范围约束的常见错误：

CHECK ( outletID BETWEEN 100 AND 200 )

CHECK ( outletID BETWEEN 200 AND 300 )

这是错误的，因为不清楚键值200属于哪一个子表。

像下面这样创建子表会更好：

CREATE TABLE measurement_y2006m02 (

CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )

) INHERITS (measurement);

CREATE TABLE measurement_y2006m03 (

CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )

) INHERITS (measurement);

...

CREATE TABLE measurement_y2007m11 (

CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )

) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (

CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )

) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (

CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )

) INHERITS (measurement);

对于每个子表，在键列上创建一个索引，以及任何想要的其他索引。

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);

CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);

CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);

CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);

CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

我们希望我们的应用能够使用INSERT INTO measurement ...并且数据将被重定向到合适的分区表。我们可以通过为主表附加一个合适的触发器函数来实现这一点。如果数据将只被增加到最后一个分区，我们可以使用一个非常简单的触发器函数：

CREATE OR REPLACE FUNCTION measurement_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

INSERT INTO measurement_y2008m01 VALUES (NEW.*);

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

完成函数创建后，我们创建一个调用该触发器函数的触发器：

CREATE TRIGGER insert_measurement_trigger

BEFORE INSERT ON measurement

FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

我们必须在每个月重新定义触发器函数，这样它才会总是指向当前的子表。而触发器的定义则不需要被更新。

我们也可能希望插入数据时服务器会自动地定位应该加入数据的子表。我们可以通过一个更复杂的触发器函数来实现之，例如：

CREATE OR REPLACE FUNCTION measurement_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.logdate >= DATE '2006-02-01' AND

NEW.logdate < DATE '2006-03-01' ) THEN

INSERT INTO measurement_y2006m02 VALUES (NEW.*);

ELSIF ( NEW.logdate >= DATE '2006-03-01' AND

NEW.logdate < DATE '2006-04-01' ) THEN

INSERT INTO measurement_y2006m03 VALUES (NEW.*);

...

ELSIF ( NEW.logdate >= DATE '2008-01-01' AND

NEW.logdate < DATE '2008-02-01' ) THEN

INSERT INTO measurement_y2008m01 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

触发器的定义和以前一样。注意每一个IF测试必须准确地匹配它的子表的CHECK约束。

当该函数比单月形式更加复杂时，并不需要频繁地更新它，因为可以在需要的时候提前加入分支。

注意

在实践中，如果大部分插入都会进入最新的子表，最好先检查它。为了简洁，我们为触发器的检查采用了和本例中其他部分一致的顺序。

把插入重定向到一个合适的子表中的另一种不同方法是在主表上设置规则而不是触发器。例如：

CREATE RULE measurement_insert_y2006m02 AS

ON INSERT TO measurement WHERE

( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )

DO INSTEAD

INSERT INTO measurement_y2006m02 VALUES (NEW.*);

...

CREATE RULE measurement_insert_y2008m01 AS

ON INSERT TO measurement WHERE

( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )

DO INSTEAD

INSERT INTO measurement_y2008m01 VALUES (NEW.*);

规则的开销比触发器大很多，但是这种开销是每个查询只有一次，而不是每行一次，因此这种方法可能对批量插入的情况有优势。不过，在大部分情况下，触发器方法将提供更好的性能。

注意COPY会忽略规则。如果想要使用COPY插入数据，则需要拷贝到正确的子表而不是直接放在主表中。COPY会引发触发器，因此在使用触发器方法时可以正常使用它。

规则方法的另一个缺点是，如果规则集合无法覆盖插入日期，则没有简单的方法能够强制产生错误，数据将会无声无息地进入到主表中。

确认constraint_exclusion配置参数在postgresql.conf中没有被禁用，否则将会不必要地访问子表。

如我们所见，一个复杂的表层次可能需要大量的DDL。在上面的例子中，我们可能为每个月创建一个新的子表，因此编写一个脚本来自动生成所需要的DDL可能会更好。

5.11.3.2. 继承分区的维护

要快速移除旧数据，只需要简单地去掉不再需要的子表：

DROP TABLE measurement_y2006m02;

要从继承层次表中去掉子表，但还是把它当做一个表保留：

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

要增加一个新子表来处理新数据，可以像上面创建的原始子表那样创建一个空的子表：

CREATE TABLE measurement_y2008m02 (

CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )

) INHERITS (measurement);

或者，用户可能想要创建新子表并且在将它加入到表层次之前填充它。这可以允许数据在被父表上的查询可见之前对数据进行装载、检查以及转换。

CREATE TABLE measurement_y2008m02

(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02

CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'

-? possibly some other data preparation work

ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.11.3.3. 提醒

下面的提醒适用于用继承实现的分区：

没有自动的方法啊验证所有的CHECK约束之间是否互斥。编写代码来产生子表以及创建和修改相关对象比手写命令要更加安全。

索引和外键约束适用于单个表而不是其继承子级，因此它们有一些caveats 需要注意。

这里展示的模式假定行的键列值从不改变，或者说改变不足以让行移动到另一个分区。由于CHECK约束的存在，尝试那样做的UPDATE将会失败。如果需要处理那种情况，可以在子表上放置适当的更新触发器，但是那会使对结构的管理更加复杂。

如果使用手工的VACUUM或者ANALYZE命令，不要忘记需要在每个子表上单独运行它们。这样的命令：

ANALYZE measurement;

将只会处理主表。

带有ON CONFLICT子句的INSERT语句不太可能按照预期工作，因为只有在指定的目标关系而不是其子关系上发生唯一违背时才会采取ON CONFLICT行动。

将会需要触发器或者规则将行路由到想要的子表中，除非应用明确地知道分区的模式。编写触发器可能会很复杂，并且会比声明式分区在内部执行的元组路由慢很多。