PostgreSQL：UPDATE隐含跨分区移动 [英] PostgreSQL: UPDATE implies move across partitions

问题描述

（注：更新为以下采用的答案。）

对于PostgreSQL 8.1（或更高版本）分区表，如何定义？一个 UPDATE 触发器和过程，以将记录从一个分区移动到另一个分区，如果 UPDATE 表示有更改

For a PostgreSQL 8.1 (or later) partitioned table, how does one define an UPDATE trigger and procedure to "move" a record from one partition to the other, if the UPDATE implies a change to the constrained field that defines the partition segregation?

例如，我将一个表记录划分为活动记录和非活动记录，例如：

For example, I've a table records partitioned into active and inactive records like so:

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 触发器和函数运行良好：新的活动记录放在一个表中，新的非活动记录放在另一个表中。我想将 UPDATE s到ACTIVE字段将记录从一个后代表移动到另一个，但是遇到一个错误，提示这可能是不可能的

The INSERT trigger and function work well: new active records get put in one table, and new inactive records in another. I would like UPDATEs to the ACTIVE field to "move" a record from one one descendant table to the other, but am encountering an error which suggests that this may not be possible.

触发规范和错误消息：

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等）向我建议，在调用触发器之前检查约束，并引发错误，这意味着我当前的方法行不通。

Playing with the trigger procedure (returning NULL and so forth) suggests to me that the constraint is checked, and the error raised, before my trigger is invoked, meaning that my current approach won't work. Can this be gotten to work?

更新/应答

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

Below is the UPDATE trigger procedure I ended up using, the same procedure assigned to each of the partitions. Credit is entirely to Bell, whose answer gave me the key insight to trigger on the partitions:

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;

推荐答案

可以使之工作，该触发器可以仅需要为每个分区而不是整个表定义移动。因此，就像您对表定义所做的一样开始，然后INSERT触发

It can be made to work, the trigger that does the move just needs to be defined for each partition, not the whole table. So start as you did for table definitions and the INSERT trigger

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

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

... let's have some test data ...

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的值时是隐式的，因为我们首先知道允许在表中显示什么。

Now the triggers on the partitions. The if NEW.active = OLD.active check is implicit in checking the value of active since we know what's allowed to be in the table in the first place.

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

...并测试结果...

... and test the results ...

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)

这篇关于PostgreSQL：UPDATE隐含跨分区移动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！