Setting up a cascading trigger on a parent table

Let's say I have started to keep a log of all the postcards that I send so I define the table postcards:

Table "postcards"
       Column         |            Type             |                Modifiers                 | Storage
----------------------+-----------------------------+------------------------------------------+----------
postcard_code         | bigint                      | not null                                 | plain    
postcard_recipient    | character varying(750)      | not null                                 | extended 
date_posted           | timestamp without time zone |                                          | plain    

Triggers:
    insert_date_posted BEFORE INSERT ON postcards FOR EACH ROW EXECUTE PROCEDURE insert_date_posted()
    update_date_posted BEFORE UPDATE ON postcards FOR EACH ROW EXECUTE PROCEDURE update_date_posted()

Child tables:
    postcards_unsent,
    postcards_2018,
    postcards_2019,
    postcards_2020

Because of the amount of postcards that I plan to send I have set up partitions via table inheritance so that all the postcards that I send this year will be logged in postcards_2018 and so on. The child tables are defined as, for example:

```
Table "postcards_2018"
Column | Type | Modifiers | Storage
----------------------+-----------------------------+------------------------------------------+----------
postcard_code | bigint | not null | plain
postcard_recipient | character varying(750) | not null | extended
date_posted | timestamp without time zone | | plain

Indexes:
"postcards_2018_pkey" PRIMARY KEY, btree ( postcard_code, postcard_recipient )

Check constraints:
"postcards_2018_date_posted_check" CHECK (
date_posted >= '2018-01-01 00:00:00'::timestamp with

We got some clarity on what the issue here was and I am finally getting around to posting this. It boils down to how triggers behave in combination with partitions (and indirectly, inheritance ?) and the clue was in the docs (5.10.6. Caveats) relating to partitioning:


The schemes shown here assume that the partition key column(s) of a
row never change, or at least do not change enough to require it to
move to another partition. An UPDATE that attempts to do that will
fail because of the CHECK constraints. If you need to handle such
cases, you can put suitable update triggers on the partition tables [...]

With our trigger, defined on the parent table, we were attempting to update the column date_posted which is the partition key and as per the snippet from the docs above, the CHECK constraint kept failing. When we defined the trigger to the child tables, it functioned as expected.

It did not occur to me sooner to try and define the trigger on the child tables as I was under the impression that FOR EACH ROW BEFORE triggers were not allowed on child tables.

We are using version 9.5 and my senior mentioned that this changes in the subsequent releases. I cloned the PostgreSQL Git repository and checked the commit logs for any clues and found:

• https://github.com/Aloisius/postgresql/commit/86f575948c773b0ec5b0f27066e37dd93a7f0a96

This commit states that:


Previously, FOR EACH ROW triggers were not allowed in partitioned
tables. Now we allow AFTER triggers on them, and on trigger creation we
cascade to create an identical trigger in each partition.

Except we used row level (FOR EACH ROW) BEFORE triggers and the version 10 docs state that:


These types of triggers may only be defined on non-partitioned tables
[...]

However, the version 9.5 and version 9.6 docs say:


These types of triggers may only be defined on tables [...]

Hence, we only got some clarity. In the meantime, the trigger works.

StackExchange Database Administrators Q#205146, answer score: 2