(注:更新了下面采用的答案。)
对于 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;
The INSERT
触发器和函数运行良好:新的活动记录放入一个表中,新的非活动记录放入另一个表中。我想UPDATE
s 到 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_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's [列表分区][2] 似乎是实现此目的的最简单方法:
-- untested!
create table RECORDS (..., ACTIVE boolean...)
partition by list(ACTIVE) (
partition ACTIVE_RECORDS values (true),
partition INACTIVE_RECORDS values (false)
)
更新/解答
下面是UPDATE
我最终使用了触发过程,与分配给每个分区的过程相同。信用完全是为了Bell https://stackoverflow.com/users/28158/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;