Having multiple trigger events when redirecting insertions to partition tables

Question

I am trying to set up triggers for insert and update events for the master table of some partition tables in PostgreSQL. Each time an insertion is made into the master table, the insert trigger event will redirect it into the correct partition table. Consequently, I will need to return NULL from this function call, since I don't want the master table to be populated as well. If the master table receives an update event, it will update a timestamp before making the change in the table. The problem is that the update trigger is never fired. I am using PostgreSQL version 9.6.

I have tried to combine the trigger functions into one, and merged the called trigger procedures into one as well, but the results are the same. The update trigger is only triggered if I return NEW from the insertion trigger function (which populates the master table), or if I comment out the insertion trigger function altogether.

DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
SET SCHEMA 'test';

CREATE TYPE test_type AS ENUM ('unit', 'performance');

CREATE TABLE test (
    type test_type NOT NULL,
    score INTEGER NOT NULL CHECK (score > 0),
    id SERIAL PRIMARY KEY,
    updated_at TIMESTAMP DEFAULT current_timestamp
);

CREATE TABLE performance_test (
    CHECK (type = 'performance')
) INHERITS (test);

CREATE FUNCTION insert_test()
RETURNS trigger AS
$$
BEGIN
    INSERT INTO performance_test VALUES (NEW.*);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION update_timestamp()
RETURNS trigger AS
$$
BEGIN
    RAISE NOTICE 'This is never reached.';

    UPDATE performance_test
    SET updated_at = current_timestamp
    WHERE id = NEW.id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insertion BEFORE INSERT ON test
    FOR EACH ROW EXECUTE PROCEDURE insert_test();

CREATE TRIGGER test_update BEFORE UPDATE ON test
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

---------------------------------------------------------------------------

INSERT INTO test VALUES ('performance', 10);

SELECT * FROM performance_test;

UPDATE test SET score = 20 WHERE id = 1;

SELECT * FROM performance_test;

I am not sure if it is possible to achieve what I want with this method, so I'm reaching out here for any advice. Thanks in advance!

/ Hampus

Answer 1

Row triggers must be defined on individual partitions, not the partitioned table. See https://www.postgresql.org/docs/10/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-LIMITATIONS

I don't know why the documentation for 9.6 doesn't mention this

working update trigger:

CREATE FUNCTION update_timestamp()
RETURNS trigger AS
$$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_update BEFORE UPDATE ON performance_test
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

if you do UPDATE test SET score = 30, updated_at=DEFAULT; or UPDATE test SET score = 30, updated_at=current_timestamp; you might not need the update trigger.

Partitioning is not a free lunch because it has non-obvious effects on both behavior and performance, as you noticed by the trigger not behaving as you expected. If you make a mistake it can easily lead to failing queries and even bad data.

If you are really sure you need it you should make sure you understand it in detail and otherwise I'd recommend you to avoid it, most issues with slow queries can be solved by making sure the table statistics is up to date, using the right indexes, optimizing queries, changing Postgres configuration or adding more hardware.