The Art of PostgreSQL/

...

/

PostgreSQL Event Publication System

PostgreSQL Event Publication System

Understand the event publication system in PostgreSQL.

We'll cover the following...

To maintain a cache of the action counters either by day or by messageid, we could write a trigger. This implements event-driven processing but kills our concurrency and scalability properties.

Trigger for notification

It’s possible for our trigger to notify an external client. This client must be a daemon program, which uses listen to register our messages. Each time a notification is sent, the daemon program processes it as necessary, possibly updating our twcache.counters table. As we have a single daemon program listening to notifications and updating the cache, we now bypass the concurrency issues.

Implement the trigger

Before implementing the client application, we can implement the trigger for notification and use psql as a testing client:

Press + to interact
begin;
create or replace function twcache.tg_notify_counters ()
 returns trigger
 language plpgsql
as $$
declare
  channel text := TG_ARGV[0];
begin
  PERFORM (
     with payload(messageid, rts, favs) as
     (
       select NEW.messageid,
              coalesce(
                 case NEW.action
                   when 'rt'    then  1
                   when 'de-rt' then -1
                  end,
                 0
              ) as rts,
              coalesce(
                case NEW.action
                  when 'fav'    then  1
                  when 'de-fav' then -1
                 end,
                0
              ) as favs
     )
     select pg_notify(channel, row_to_json(payload)::text)
       from payload
  );
  RETURN NULL;
end;
$$;
CREATE TRIGGER notify_counters
         AFTER INSERT
            ON tweet.activity
      FOR EACH ROW
       EXECUTE PROCEDURE twcache.tg_notify_counters('tweet.activity');
commit;

Test the trigger

We can test the trigger by issuing the following statement at ...