Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

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.