PGSQL Phriday #007 – A word about triggers

I am two days late for the PGSQL Phriday #007 deadline, but I can’t skip the question asked by Laeticia Avrot: do you love triggers? Do you hate triggers? Also, I really enjoyed reading Ryan Booz’s response. I can relate to many of his statements about triggers.

Since Ryan already said everything I could possibly say about triggers, I will be brief. Triggers can do a lot of harm! Triggers can go really wrong – those who attended Nordic PG Day are sure to remember this war story. I do not support those who say that triggers should be banned, but I understand their frustration.

Still, PostgreSQL is impossible without triggers. Did you hear about internal triggers? Postgres creates these triggers behind the scene to support constraints (how would you support them otherwise?!). When I wanted to add foreign key constraints to pg_bitemporal, I had to create a bitemporal version of these triggers.

Triggers are exceptionally powerful, which is both good and bad. I believe that there is no problem in Postgres that you can’t solve by creating triggers that will do exactly what you want. A couple of months ago, I complained about permissions in Postgres: what I do not like in the default permissions setup and what I would change. Since patience is not my virtue, and since I do not like to complain and prefer “being a change,” I decided to model my ideal permissions settings. I achieved it by building a system of event triggers. I am not going to lie – the result is inhumanly complex, but event triggers made it possible.

My short summary for this short post is the following: please stop pretending that we can live without triggers. We can’t!