PostgreSQL：UPDATE意味着跨越分区

概述(注：更新为以下答案。) 对于PostgreSQL 8.1(或更高版本)分区表，如何定义UPDATE触发器和过程以将记录从一个分区“移动”到另一个分区，如果UPDATE意味着更改限制字段来定义分区隔离？ 例如，我将表记录分为活动和非活动记录，如下所示： create table RECORDS (RECORD varchar(64) not null, ACTIVE boolean default (注：更新为以下答案。)

对于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触发器和函数工作正常：将新的活动记录放在一个表中，并将新的非活动记录放在另一个表中。我希望UPDATEs的ACTIVE字段将一个记录从一个后代表移动到另一个，但我遇到一个错误，这表明这可能是不可能的。

触发器规格和错误信息：

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_RECORDSbar    | 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等)建议我检查约束，并且在调用触发器之前引发错误，这意味着我当前的方法不起作用。这可以得到工作吗？

UPDATE / ANSWER

以下是我最终使用的UPDATE触发程序，分配给每个分区的相同过程。信用完全是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;

它可以使其工作，只需要为每个分区而不是整个表定义移动的触发器。所以就像对表定义和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);

现在分区上的触发器。如果NEW.active = olD.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 0scratch=> 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意味着跨越分区全部内容，希望文章能够帮你解决PostgreSQL：UPDATE意味着跨越分区所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。