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;

user_8520235 · Answer

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 |

PostgreSQL: UPDATE implies move across partitions

2 Answers