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?

ADDITIONAL ANSWER

pg's [list partitioning][2] appears to be the easiest way to accomplish this:

-- untested!
create table RECORDS (..., ACTIVE boolean...)
partition by list(ACTIVE) (
  partition   ACTIVE_RECORDS values (true),
  partition INACTIVE_RECORDS values (false)
) 

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

Beware that you can partition by list and let the database do all the hard work to move rows among partitions. (untested for 8.4 but most probably working, as for pilcrow comment).

In the following example, a table is created and partitioned by list, using one of the columns in the primary key.

create table t (
  -- natural primary key
  doc_type varchar not null default 'PRODUCT',
  doc_id   int not null generated always as identity,
  
  -- content columns
  title    varchar not null,
  
  -- primary key
  primary key (doc_type, doc_id)
)
partition by list(doc_type);

-- partitions of t
create table t_product partition of t for values in ('PRODUCT');
create table t_default partition of t default;

Then we insert some data that should end in t_product or t_default, depending on the value of doc_type.

insert into t (doc_type, title) values
('PRODUCT', 'My first product'),  -- 1
('ARTICLE', 'My first article'),  -- 2
('TOPIC',   'My first topic'),    -- 3
('PRODUCT', 'My second product'), -- 4
('PRODUCT', 'My third product'),  -- 5
('ARTICLE', 'My second article'), -- 6
('TOPIC',   'My second topic'),   -- 7
('PRODUCT', 'My fourth product'); -- 8

We check rows are automatically moved to the right table

select * from t_product;

doc_type|doc_id|title            |
--------+------+-----------------+
PRODUCT |     1|My first product |
PRODUCT |     4|My second product|
PRODUCT |     5|My third product |
PRODUCT |     8|My fourth product|

Now, let us convert a PRODUCT into an ARTICLE to see what happens.

update t
set    doc_type = 'ARTICLE'
where  doc_type = 'PRODUCT'
and    doc_id = 1;

It can be seen the row is not in the t_product partition anymore

select * from t_product;
doc_type|doc_id|title            |
--------+------+-----------------+
PRODUCT |     4|My second product|
PRODUCT |     5|My third product |
PRODUCT |     8|My fourth product|

but in the t_default partition.

doc_type|doc_id|title            |
--------+------+-----------------+
ARTICLE |     2|My first article |
TOPIC   |     3|My first topic   |
ARTICLE |     6|My second article|
TOPIC   |     7|My second topic  |
ARTICLE |     1|My first product |

Answer 2

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)