Postgresql UPDATE implies move across partitions

它可以工作，执行移动的触发器只需要 for each 分区定义，而不是为整个表定义.所以，从表定义和插入触发器开始

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);

现在是分区上的触发器.如果新的话.活跃=旧.active check隐含在判断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)