This question happens every now and then in one of PostgreSQL support places.

Whenever it happens, I just suggest to not try, as interacting with outside world from database can be problematic, and instead use LISTEN/NOTIFY.

But it occurred to me, that while I know how to do it, I don't think I actually did it. It being: handle listen/notify in real life code. So let's try. I will not be writing actual email sending or http requesting, but will make sure that my program will get, from database, information when NOTIFY happens. How hard could it be?

I usually write such example in Perl, but recently someone pointed out that it's old, and not really “hip" language. So I'll try in Ruby. But first I need to make sure it works in psql.

To do it I start two psql sessions: psql1 and psql2. Then:

1. in psql1, I run: LISTEN depesz;
2. in psql2, I run: NOTIFY depesz, 'test1';
3. in psql1 – well, nothing happened. So, just to be sure, I ran stupid query, just to get interaction with server:

   psql1> SELECT 1;
    ?COLUMN?
   ──────────
           1
   (1 ROW)
    
   Asynchronous notification "depesz" WITH payload "test1" received FROM server process WITH PID 97882.

Sweet. So, I have to do some kind of interaction with server, to get the notification. At least in psql. Let's see how ruby will work with it.

First, I need to have library to connect to Pg. Luckily it's simple:

=$ gem install --user-install pg
…
1 gem installed

First thing, of course, I have to connect to db, and make sure that connection works. This should be trivial:

#!/usr/bin/env ruby
# frozen_string_literal: true
require 'pg'
conn = PG.connect( :dbname => 'depesz' )
res = conn.exec("SELECT current_database(), current_user, now()")
pp res[0]

After running it, I got expected output:

=$ ./test1.rb
{"current_database"=>"depesz", "current_user"=>"depesz", "now"=>"2024-11-25 22:38:30.681358+01"}

Sweet. So I have connection, it works. Now, let's see how to handle LISTENing. Luckily docs for the gem show wait_for_notify method. Sounds promising.

Could that be it? Of course, waiting for notification doesn't mean that I can't skip doing LISTEN, but that should be trivial. So, let's see:

#!/usr/bin/env ruby
# frozen_string_literal: true
require 'pg'
conn = PG.connect( :dbname => 'depesz' )
res = conn.exec("LISTEN ruby")
conn.wait_for_notify do | event, pid, payload |
  pp({event: event, pid: pid, payload: payload})
end

Started this, and then in psql I ran: NOTIFY ruby, ‘test2';. In the console with ruby code, I immediately saw:

{:event=>"ruby", :pid=>97882, :payload=>"test2"}

And then my program ended.

Well, it makes sense – it waited, got some notification, printed, and that's all.

Luckily we can solve this, by adding there a loop.

Also, a note that might be important. The PID is process id of backend of PostgreSQL that ran the notify.

So, let's add a loop, and while at it, let's just wait 10 seconds, and print some debug information. Updated code:

#!/usr/bin/env ruby
# frozen_string_literal: true
require 'pg'
conn = PG.connect( :dbname => 'depesz' )
res = conn.exec("LISTEN ruby")
loop do
  got_notifications = false
  conn.wait_for_notify(10) do | event, pid, payload |
    pp({event: event, pid: pid, payload: payload})
    got_notifications = true
  end
  if ! got_notifications
    puts "No notifications in the last 10 seconds."
  end
end

Started it, and waited over 10 seconds, verified that I got the “No notifications…" message, and then 3 notified in a row. Result:

=$ ./test1.rb
No notifications in the last 10 seconds.
{:event=>"ruby", :pid=>109479, :payload=>"test3"}
{:event=>"ruby", :pid=>109479, :payload=>"test4"}
{:event=>"ruby", :pid=>109479, :payload=>"test5"}

So, it's all good now. With this code, I can send mails from outside of db, without blocking any transaction, and all working great.

There is one more thing that you should know. Notifications get to listener not when client calls NOTIFY, but when transaction that did it, commits. And only commits. If it rolls back – notification is not sent.

We can see it using slightly modified test script:

#!/usr/bin/env ruby
# frozen_string_literal: TRUE
require 'pg'
conn = PG.connect( :dbname => 'depesz' )
res = conn.exec("LISTEN ruby")
loop do
  conn.wait_for_notify(10) do | event, pid, payload |
    puts "Got notification with payload #{payload} at #{Time.now}"
  END
END

Now, I want to pass information to notification on when it was generated. Unfortunately I can't do things like:

NOTIFY ruby, 'testing with clock: ' || clock_timestamp();
NOTIFY ruby, 'testing with clock: ' || clock_timestamp()::text;
NOTIFY ruby, 'testing with clock: '::text || clock_timestamp()::text;
NOTIFY ruby, clock_timestamp();

they all end up with syntax error. So, instead I will use psql's \gexec trick, I will write a query that generates “NOTIFY" command, and then will \gexec it:

=$ SELECT format( 'notify ruby, %L', clock_timestamp() );
                    format
──────────────────────────────────────────────
 notify ruby, '2024-11-25 22:58:14.125258+01'
(1 ROW)
 
=$ SELECT format( 'notify ruby, %L', clock_timestamp() ) \gexec
NOTIFY

In console with my listener, I saw:

Got notification with payload 2024-11-25 22:58:24.844157+01 at 2024-11-25 22:58:24 +0100

Please note that the time is basically the same. Payload is more precise, but at least seconds match.

But, let's assume I will start transaction, do notify, and then wait 5 seconds, and only then commit:

$ BEGIN;
BEGIN
 
*$ SELECT format( 'notify ruby, %L', clock_timestamp() ) \gexec
NOTIFY
 
*$ SELECT pg_sleep(5);
 pg_sleep
──────────
 
(1 ROW)
 
*$ commit;
COMMIT

Listener outputted:

Got notification with payload 2024-11-25 23:00:24.522604+01 at 2024-11-25 23:00:34 +0100

Notification was generated at …:24 seconds, but received only at …:34. (took me some time to type the commands in psql).

Thanks to this, you don't have to worry about what will happen if you will get notification, but the transaction will get rolled back – in such case, you will not get it. Plus, when you get the notification, all data that were written to the DB in the transaction that issued notify – are already fully visible, as they were committed. Nice.

All in all, I think it's pretty simple thing to write. Of course, after you reach some levels of notification traffic, I'd say that you should just use some ready-made job queue (which internally uses LISTEN/NOTIFY, I bet).

Hope you'll find it helpful.