PostgreSQL：更新意味着跨分区移动

Question

（注意：更新了下面采用的答案。）

对于 PostgreSQL 8.1（或更高版本）分区表，如何定义 UPDATE 触发器和过程来“移动”记录从一个分区到另一个分区，如果UPDATE意味着对定义分区隔离的约束字段的更改？

例如，我有一个表记录分为活动记录和非活动记录，如下所示：

create table RECORDS (RECORD varchar(64) not null, ACTIVE boolean default true);
create table ACTIVE_RECORDS   ( check (ACTIVE) ) inherits RECORDS;
create table INACTIVE_RECORDS ( check (not ACTIVE) ) inherits RECORDS;

INSERT 触发器和函数运行良好：新的活动记录放入一个表中，新的非活动记录放入另一个表中。我希望对 ACTIVE 字段进行 UPDATE 来将记录从一个后代表“移动”到另一个后代表，但遇到一个错误，表明这可能是不可能的。

触发器规范和错误消息：

pg=> CREATE OR REPLACE FUNCTION record_update()
     RETURNS TRIGGER AS $$
     BEGIN
       IF (NEW.active = OLD.active) THEN
         RETURN NEW;
       ELSIF (NEW.active) THEN
         INSERT INTO active_records VALUES (NEW.*);
         DELETE FROM inactive_records WHERE record = NEW.record;
       ELSE
         INSERT INTO inactive_records VALUES (NEW.*);
         DELETE FROM active_records WHERE record = NEW.record;
       END IF;
       RETURN NULL;
     END;
     $$
     LANGUAGE plpgsql;
     
pg=> CREATE TRIGGER record_update_trigger
       BEFORE UPDATE ON records
       FOR EACH ROW EXECUTE PROCEDURE record_update();

pg=> select * from RECORDS;
record | active 
--------+--------
foo    | t         -- 'foo' record actually in table ACTIVE_RECORDS
bar    | f         -- 'bar' record actually in table INACTIVE_RECORDS
(2 rows)

pg=> update RECORDS set ACTIVE = false where RECORD = 'foo';
ERROR:  new row for relation "active_records" violates check constraint "active_records_active_check"

使用触发器过程（返回 NULL 等）建议我在调用触发器之前检查约束并引发错误，这意味着我当前的方法将不起作用。这可以开始工作吗？

附加答案

pg的[列表分区][2]似乎是实现此目的的最简单方法：

-- untested!
create table RECORDS (..., ACTIVE boolean...)
partition by list(ACTIVE) (
  partition   ACTIVE_RECORDS values (true),
  partition INACTIVE_RECORDS values (false)
) 

更新/答案

下面是我最终使用了更新触发过程，分配给每个分区的过程相同。完全归功于 Bell，他的回答为我提供了在分区上触发的关键见解：

CREATE OR REPLACE FUNCTION record_update()
RETURNS TRIGGER AS $$
BEGIN
  IF ( (TG_TABLE_NAME = 'active_records' AND NOT NEW.active)
        OR
       (TG_TABLE_NAME = 'inactive_records' AND NEW.active) ) THEN
    DELETE FROM records WHERE record = NEW.record;
    INSERT INTO records VALUES (NEW.*);
    RETURN NULL;
  END IF;

  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Answer 1

它可以工作，只需要为每个分区定义执行移动的触发器，而不是整个表。因此，像表定义和 INSERT 触发器一样开始

CREATE TABLE records (
 record varchar(64) NOT NULL,
 active boolean default TRUE
);

CREATE TABLE active_records (CHECK (active)) INHERITS (records);
CREATE TABLE inactive_records (CHECK (NOT active)) INHERITS (records);

CREATE OR REPLACE FUNCTION record_insert()
RETURNS TRIGGER AS $
BEGIN
  IF (TRUE = NEW.active) THEN
    INSERT INTO active_records VALUES (NEW.*);
  ELSE
    INSERT INTO inactive_records VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$
LANGUAGE plpgsql;

CREATE TRIGGER record_insert_trigger
 BEFORE INSERT ON records
 FOR EACH ROW EXECUTE PROCEDURE record_insert();

...让我们有一些测试数据...

INSERT INTO records VALUES ('FirstLittlePiggy', TRUE);
INSERT INTO records VALUES ('SecondLittlePiggy', FALSE);
INSERT INTO records VALUES ('ThirdLittlePiggy', TRUE);
INSERT INTO records VALUES ('FourthLittlePiggy', FALSE);
INSERT INTO records VALUES ('FifthLittlePiggy', TRUE);

现在是分区上的触发器。 if NEW.active = OLD.active 检查隐式检查 active 的值，因为我们首先知道表中允许包含哪些内容。

CREATE OR REPLACE FUNCTION active_partition_constraint()
  RETURNS TRIGGER AS $
    BEGIN
      IF NOT (NEW.active) THEN
        INSERT INTO inactive_records VALUES (NEW.*);
        DELETE FROM active_records WHERE record = NEW.record;
        RETURN NULL;
      ELSE
        RETURN NEW;
      END IF;
    END;
    $
    LANGUAGE plpgsql;

CREATE TRIGGER active_constraint_trigger
  BEFORE UPDATE ON active_records
  FOR EACH ROW EXECUTE PROCEDURE active_partition_constraint();

CREATE OR REPLACE FUNCTION inactive_partition_constraint()
  RETURNS TRIGGER AS $
    BEGIN
      IF (NEW.active) THEN
        INSERT INTO active_records VALUES (NEW.*);
        DELETE FROM inactive_records WHERE record = NEW.record;
        RETURN NULL;
      ELSE
        RETURN NEW;
      END IF;
    END;
    $
    LANGUAGE plpgsql;

CREATE TRIGGER inactive_constraint_trigger
  BEFORE UPDATE ON inactive_records 
  FOR EACH ROW EXECUTE PROCEDURE inactive_partition_constraint();

...并测试结果...

scratch=> SELECT * FROM active_records;
      record      | active 
------------------+--------
 FirstLittlePiggy | t
 ThirdLittlePiggy | t
 FifthLittlePiggy | t
(3 rows)

scratch=> UPDATE records SET active = FALSE WHERE record = 'ThirdLittlePiggy';
UPDATE 0
scratch=> SELECT * FROM active_records;
      record      | active 
------------------+--------
 FirstLittlePiggy | t
 FifthLittlePiggy | t
(2 rows)

scratch=> SELECT * FROM inactive_records;
      record       | active 
-------------------+--------
 SecondLittlePiggy | f
 FourthLittlePiggy | f
 ThirdLittlePiggy  | f
(3 rows)

Answer 2

请注意，您可以按列表分区，并让数据库完成在分区之间移动行的所有艰苦工作。
（未经 8.4 测试，但很可能有效，正如 pilcrow 的评论）。

在以下示例中，使用主键中的列之一创建了一个表，并按列表对其进行分区。

create table t (
  -- natural primary key
  doc_type varchar not null default 'PRODUCT',
  doc_id   int not null generated always as identity,
  
  -- content columns
  title    varchar not null,
  
  -- primary key
  primary key (doc_type, doc_id)
)
partition by list(doc_type);

-- partitions of t
create table t_product partition of t for values in ('PRODUCT');
create table t_default partition of t default;

然后，我们插入一些应以 t_product 或 t_default 结尾的数据，具体取决于 doc_type 的值。

insert into t (doc_type, title) values
('PRODUCT', 'My first product'),  -- 1
('ARTICLE', 'My first article'),  -- 2
('TOPIC',   'My first topic'),    -- 3
('PRODUCT', 'My second product'), -- 4
('PRODUCT', 'My third product'),  -- 5
('ARTICLE', 'My second article'), -- 6
('TOPIC',   'My second topic'),   -- 7
('PRODUCT', 'My fourth product'); -- 8

我们检查行是否自动移至右侧表格

select * from t_product;

doc_type|doc_id|title            |
--------+------+-----------------+
PRODUCT |     1|My first product |
PRODUCT |     4|My second product|
PRODUCT |     5|My third product |
PRODUCT |     8|My fourth product|

现在，让我们将 PRODUCT 转换为 ARTICLE 看看会发生什么。

update t
set    doc_type = 'ARTICLE'
where  doc_type = 'PRODUCT'
and    doc_id = 1;

可以看到该行不再位于 t_product 分区中，

select * from t_product;
doc_type|doc_id|title            |
--------+------+-----------------+
PRODUCT |     4|My second product|
PRODUCT |     5|My third product |
PRODUCT |     8|My fourth product|

而是位于 t_default 分区中。

doc_type|doc_id|title            |
--------+------+-----------------+
ARTICLE |     2|My first article |
TOPIC   |     3|My first topic   |
ARTICLE |     6|My second article|
TOPIC   |     7|My second topic  |
ARTICLE |     1|My first product |