Search⌘ K
Home
Courses
The Art of PostgreSQL

Fixing the Behavior

Explore how to identify and correct problematic trigger implementations in PostgreSQL. Understand transactional trigger processing and concurrency issues, and learn to implement efficient triggers with practical examples that enhance data consistency and performance.

We'll cover the following...

While it’s easy to maintain cache in an event-driven fashion thanks to PostgreSQL and its trigger support. Turning an insert into an update with contention on a single row is never a good idea. It’s even a classic anti-pattern.

Script for triggers

Here’s a modern way to fix the problem with the previous trigger implementation, this time applied to a per-message counter of Retweet and favorite actions:

PostgreSQL
begin;
create table twcache.counters
 (
   messageid  bigint not null references tweet.message(messageid),
   rts        bigint,
   favs       bigint,
   unique(messageid)
 );
create or replace function twcache.tg_update_counters ()
 returns trigger
 language plpgsql
as $$
declare
begin
   insert into twcache.counters(messageid, rts, favs)
        select NEW.messageid,
               case when NEW.action = 'rt' then 1 else 0 end,
               case when NEW.action = 'fav' then 1 else 0 end
   on conflict (messageid)
     do update
           set rts = case when NEW.action = 'rt'
                          then counters.rts + 1
                          when NEW.action = 'de-rt'
                          then counters.rts - 1
                          else counters.rts
                      end,
               favs = case when NEW.action = 'fav'
                           then counters.favs + 1
                           when NEW.action = 'de-fav'
                           then counters.favs - 1
                           else counters.favs
                       end
         where counters.messageid = NEW.messageid;
  RETURN NULL;
end;
$$;
CREATE TRIGGER update_counters
         AFTER INSERT
            ON tweet.activity
      FOR EACH ROW
       EXECUTE PROCEDURE twcache.tg_update_counters();
insert into tweet.activity(messageid, action)
     values (7, 'rt'),
            (7, 'fav'),
            (7, 'de-fav'),
            (8, 'rt'),
            (8, 'rt'),
            (8, 'rt'),
            (8, 'de-rt'),
            (8, 'rt');
select messageid, rts, favs
  from twcache.counters;
rollback;

And ...