Trigger and Counters Anti-Pattern
Explore the concurrency challenges that arise from using triggers to update daily counters in PostgreSQL. Understand how typical upsert implementations can fail when multiple transactions run simultaneously and learn improved methods to handle such conflicts, including the use of exception handling loops and the on conflict clause introduced in PostgreSQL 9.5. This lesson helps you grasp practical solutions to ensure reliable transactional counting.
We'll cover the following...
We might also notice that this trigger is very wrong in its behavior, as coded earlier. The implementation of the insert or update— aka upsert—is coded in a way to leave the door open to concurrency issues.
Concurrency issues
To understand the concurrency issues, we need to consider what happens when we start a new day:
-
The first transaction of the day attempts to update the daily counters table for this day but finds no records because it’s the first one. ...