PostgreSQL: UPDATE implies move across partitions

Question

(Note: updated with adopted answer below.)

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; 

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.

Trigger specification and error message:

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" 

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/ANSWER

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; 

Answer 1

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

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)