On 18th of March 2020, Alvaro Herrera committed patch:
Enable BEFORE row-level triggers for partitioned tables ... with the limitation that the tuple must remain in the same partition. Reviewed-by: Ashutosh Bapat Discussion: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql
So, I never made triggers on partitioned tables, so I was kinda sure that they would work anyway. So I tried:
=$ CREATE TABLE users ( username text NOT NULL PRIMARY KEY, fullname text ) partition BY hash (username); =$ CREATE TABLE users_0 partition OF users FOR VALUES WITH (modulus 2, remainder 0); =$ CREATE TABLE users_1 partition OF users FOR VALUES WITH (modulus 2, remainder 1);
and then:
=$ CREATE OR REPLACE FUNCTION proper_fullname() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN NEW.fullname := initcap( NEW.fullname ); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; =$ CREATE TRIGGER proper_fullname BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE proper_fullname();
This broke on PostgreSQL 12:
ERROR: "users" IS a partitioned TABLE DETAIL: Partitioned TABLES cannot have BEFORE / FOR EACH ROW triggers.
But of course, I can work around by creating the trigger on each partition itself:
=$ CREATE TRIGGER proper_fullname BEFORE INSERT OR UPDATE ON users_0 FOR EACH ROW EXECUTE PROCEDURE proper_fullname(); =$ CREATE TRIGGER proper_fullname BEFORE INSERT OR UPDATE ON users_1 FOR EACH ROW EXECUTE PROCEDURE proper_fullname();
Afterwards, still on Pg12:
=$ INSERT INTO users (username, fullname) VALUES ('depesz', 'hubert lubaczewski'); INSERT 0 1 =$ SELECT * FROM users; username │ fullname ──────────┼──────────────────── depesz │ Hubert Lubaczewski (1 ROW)
=$ create table users (
username text not null primary key,
fullname text
) partition by hash (username);
=$ create table users_0 partition of users for values with (modulus 2, remainder 0);
=$ create table users_1 partition of users for values with (modulus 2, remainder 1);
So, it works. true. But then – I'd have to remember about adding the trigger on all partitions, always.
On the other hand – initial create trigger on users, worked fine on Pg 13. It propagated the trigger to all partitions:
$ \d users
Partitioned TABLE "public.users"
COLUMN │ TYPE │ Collation │ NULLABLE │ DEFAULT
──────────┼──────┼───────────┼──────────┼─────────
username │ text │ │ NOT NULL │
fullname │ text │ │ │
Partition KEY: HASH (username)
Indexes:
"users_pkey" PRIMARY KEY, btree (username)
Triggers:
proper_fullname BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION proper_fullname()
NUMBER OF partitions: 2 (USE \d+ TO list them.)
=$ \d users_0
TABLE "public.users_0"
COLUMN │ TYPE │ Collation │ NULLABLE │ DEFAULT
──────────┼──────┼───────────┼──────────┼─────────
username │ text │ │ NOT NULL │
fullname │ text │ │ │
Partition OF: users FOR VALUES WITH (modulus 2, remainder 0)
Indexes:
"users_0_pkey" PRIMARY KEY, btree (username)
Triggers:
proper_fullname BEFORE INSERT OR UPDATE ON users_0 FOR EACH ROW EXECUTE FUNCTION proper_fullname()
=$ \d users_1
TABLE "public.users_1"
COLUMN │ TYPE │ Collation │ NULLABLE │ DEFAULT
──────────┼──────┼───────────┼──────────┼─────────
username │ text │ │ NOT NULL │
fullname │ text │ │ │
Partition OF: users FOR VALUES WITH (modulus 2, remainder 1)
Indexes:
"users_1_pkey" PRIMARY KEY, btree (username)
Triggers:
proper_fullname BEFORE INSERT OR UPDATE ON users_1 FOR EACH ROW EXECUTE FUNCTION proper_fullname()and, of course, it also works:
=$ INSERT INTO users (username, fullname) VALUES ('depesz', 'hubert lubaczewski'); INSERT 0 1 =$ SELECT * FROM users; username │ fullname ──────────┼──────────────────── depesz │ Hubert Lubaczewski (1 ROW)
The great thing is that if I'd add new partition to such table (less likely with has based partitions, but more likely for range based) – trigger will automatically be created in new partition as well.
This couldn't have happened in previous Pg version, as main table, didin't have any information about triggers on partitions.
Great stuff, thanks to all involved.