jcuervo · December 13, 2024 06:34 · Dec 13, 2024 · Dec 13, 2024 · Dec 13, 2024 · Dec 13, 2024
diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -70,6 +70,7 @@ This migration will set up the trigger and function that will enable real-time n
 A background job or a dedicated thread can handle the `NOTIFY` messages in Rails. The `pg` gem provides the necessary tools to subscribe via `LISTEN` to channels.
 
 ```
+# app/services/postgres_listener.rb
 class PostgresListener
   def listen
     ActiveRecord::Base.connection_pool.with_connection do |connection|
@@ -133,6 +134,7 @@ The code above only returns two fields from the payload. Also, notice the `NEW.i
 Create a rake file, `database_listener.rake` to call the `PostgresListener` and `listen`.
 
 ```
+# lib/tasks/database_listener.rake
 namespace :database_listener do
 
   desc 'listen to PG notifications'

diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -156,4 +156,13 @@ release: rake db:migrate
 
 ## Use Cases
 
-For Rails applications in Heroku that is integrated into Salesforce via Heroku Connect, records created on Salesforce doesn't trigger the ActiveRecord's callbacks. If your requirement is to react on specific events, the only way to get this is via the `NOTIFY` and `TRIGGER` features of PostGreSQL on the database level.
+For Rails applications in Heroku that is integrated into Salesforce via Heroku Connect, records created on Salesforce doesn't trigger the ActiveRecord's callbacks. If your requirement is to react on specific events, the only way to get this is via the `NOTIFY` and `TRIGGER` features of PostGreSQL on the database level.
+
+Another application is keeping distributed systems in sync. Notifications from one service’s database can trigger actions in another service, ensuring data consistency across the ecosystem.
+
+## Performance Considerations and Security Implications
+
+`NOTIFY` and `LISTEN` are powerful but can affect database performance. The notifications has some overhead and excessive usage can affect the database's operations. Avoid triggering notification for every minor change.
+
+Sanitize payloads to prevent injection attacks and validate incoming messages to ensure they’re from trusted sources.
+
diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -122,6 +122,14 @@ For this example, `postgres_listener.rb` is saved inside the `/app/services` fol
 
 Just a heads-up on the payload, it is in JSON format. If you have `alias_attributes` in the model for the specific table that you are monitoring, the actual field names will be used as reference. Example, `record['first_name__c']` will be used if you have `alias_attribute :first_name, :first_name__c` in the `Invoice` model.
 
+Another thing to note is the `payload` is maxed at 8000 bytes. If you are monitoring a table with a lot of fields, you might want to limit the fields that you want to return on the `NOTIFY` part.
+
+```
+PERFORM pg_notify('invoice_updated', '{ "reference_id": "' || COALESCE(NEW.reference_id) || '", "invoice_status":  "' || COALESCE(NEW.invoice_status) || '" }');
+```
+
+The code above only returns two fields from the payload. Also, notice the `NEW.invoice_status` reference? That means you can also return the `OLD.invoice_status` field value if your business logic requires it.
+
 Create a rake file, `database_listener.rake` to call the `PostgresListener` and `listen`.
 
 ```

diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -118,7 +118,9 @@ class PostgresListener
 end
 ```
 
-For this example, `postgres_listener.rb` is saved inside the `/app/services` folder of the Rails project.
+For this example, `postgres_listener.rb` is saved inside the `/app/services` folder of the Rails project. We begin by looping into the connection pool and listen to specific channels. As soon as a broadcast message is transmitted, we can handle it accordingly based on the channel and the payload that it sends.
+
+Just a heads-up on the payload, it is in JSON format. If you have `alias_attributes` in the model for the specific table that you are monitoring, the actual field names will be used as reference. Example, `record['first_name__c']` will be used if you have `alias_attribute :first_name, :first_name__c` in the `Invoice` model.
 
 Create a rake file, `database_listener.rake` to call the `PostgresListener` and `listen`.
 
@@ -131,4 +133,19 @@ namespace :database_listener do
     listener.listen
   end
 end
-``
+```
+
+In Heroku, create an entry in the Procfile and provision a dyno for it. You can scale this as the need arise. The listener might be handling different notifications from a number of tables and for different events.
+
+```
+# Procfile
+web: bundle exec puma -t 5:5 -p ${PORT:-3000} -e ${RACK_ENV:-production}
+worker: bundle exec puma -t 5:5 -p ${PORT:-3000} -e ${RACK_ENV:-production}
+sidekiq: bundle exec sidekiq -c 2 -v -q default -q mailers
+listeners: bundle exec rake database_listener:listen
+release: rake db:migrate
+```
+
+## Use Cases
+
+For Rails applications in Heroku that is integrated into Salesforce via Heroku Connect, records created on Salesforce doesn't trigger the ActiveRecord's callbacks. If your requirement is to react on specific events, the only way to get this is via the `NOTIFY` and `TRIGGER` features of PostGreSQL on the database level.
diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -116,4 +116,19 @@ class PostgresListener
     HandleOtherTableUpdateJob.perform_now(record)
   end
 end
-```
+```
+
+For this example, `postgres_listener.rb` is saved inside the `/app/services` folder of the Rails project.
+
+Create a rake file, `database_listener.rake` to call the `PostgresListener` and `listen`.
+
+```
+namespace :database_listener do
+
+  desc 'listen to PG notifications'
+  task listen: :environment do
+    listener = PostgresListener.new
+    listener.listen
+  end
+end
+``
diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -63,4 +63,57 @@ class AddNotifyTriggerToInvoices < ActiveRecord::Migration[7.0]
 end
 ```
 
-This migration will set up the trigger and function that will enable real-time notifications whenever a new record is created in the `invoices` table.
+This migration will set up the trigger and function that will enable real-time notifications whenever a new record is created in the `invoices` table.
+
+## Listening for Notifications in Rails
+
+A background job or a dedicated thread can handle the `NOTIFY` messages in Rails. The `pg` gem provides the necessary tools to subscribe via `LISTEN` to channels.
+
+```
+class PostgresListener
+  def listen
+    ActiveRecord::Base.connection_pool.with_connection do |connection|
+      conn = connection.instance_variable_get(:@connection)
+
+      begin
+        conn.async_exec "LISTEN new_invoice_created"
+        conn.async_exec "LISTEN another_channel"
+        conn.async_exec "LISTEN other_table_update"
+
+        loop do
+          conn.wait_for_notify do |channel, pid, payload|
+            if payload.present?
+              record = JSON.parse(payload)
+              case channel
+              when 'new_invoice_created'
+                handle_new_invoice(record)
+              when 'another_channel'
+                handle_another_channel(record)
+              when 'other_table_update'
+                handle_other_table_update(record)
+              end
+            end
+          end
+        end
+      ensure
+        conn.async_exec "UNLISTEN *"
+      end
+    end
+  end
+
+
+  private
+
+  def new_invoice_created(record)
+    NewInvoiceCreatedJob.perform_now(record)
+  end
+
+  def handle_another_channel(record)
+    HandleOtherChannelJob.perform_now(record)
+  end
+
+  def handle_other_table_update(record)
+    HandleOtherTableUpdateJob.perform_now(record)
+  end
+end
+```
diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -30,4 +30,37 @@ END;
 $$ LANGUAGE plpgsql;
 ```
 
-This sample trigger function sends a JSON payload to the `new_invoice_created` channel whenever a new record is created.
+Combined with `NOTIFY`, this sample trigger function sends a JSON payload to the `new_invoice_created` channel whenever a new record is created.
+
+## Setting Up NOTIFY and TRIGGER Functions in Rails
+
+Start by creating a migration to define the trigger. Depending on your preferrence, you can either embed raw SQL in the migrations or create a separate SQL file and call it from there.
+
+```
+class AddNotifyTriggerToInvoices < ActiveRecord::Migration[7.0]
+  def up
+    execute <<~SQL
+      CREATE OR REPLACE FUNCTION notify_on_invoice_insert() RETURNS trigger AS $$
+      BEGIN
+        PERFORM pg_notify('new_invoice_created', row_to_json(NEW)::text);
+        RETURN NEW;
+      END;
+      $$ LANGUAGE plpgsql;
+
+      CREATE TRIGGER notify_after_invoice_insert
+      AFTER INSERT ON invoices
+      FOR EACH ROW
+      EXECUTE FUNCTION notify_on_invoice_insert();
+    SQL
+  end
+
+  def down
+    execute <<~SQL
+      DROP TRIGGER IF EXISTS notify_after_invoice_insert ON messages;
+      DROP FUNCTION IF EXISTS notify_on_invoice_insert();
+    SQL
+  end
+end
+```
+
+This migration will set up the trigger and function that will enable real-time notifications whenever a new record is created in the `invoices` table.
diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -1,5 +1,33 @@
-**Using PostgreSQL Notify and Trigger Functions in Ruby on Rails**
+# Using PostgreSQL Notify and Trigger Functions in Ruby on Rails
 
-One of the advanced features of PostgreSQL is the NOTIFY and LISTEN functionality. These features enable real-time communication between databases and applications. Modern web applications that requires event-driven behavoir and real-time updates will benefit from this.
+One of the advanced features of PostgreSQL is the `NOTIFY` and `LISTEN` functionality. These features enable real-time communication between databases and applications. Modern web applications that requires event-driven behavoir and real-time updates will benefit from this.
 
-Combining these features with Ruby on Rails can create powerful real-time applications with minimum overhead.
+Combining these features with Ruby on Rails can create powerful real-time applications with minimum overhead.
+
+## NOTIFY and LISTEN
+
+The `NOTIFY` and `LISTEN` features allows a database session to send and receive asynchoronous messages. With `NOTIFY`, a session broadcasts a message on a specific channel. `LISTEN` allows other sessions to subscribe to those messages. The mechanishm enables real-time communication and thus reducing the need for continous polling of the database.
+
+For example, a `NOTIFY` statement can be used to alert other sessions that a new record was created.
+
+```
+NOTIFY new_invoice_created, 'payload';
+LISTEN new_invoice_created
+```
+
+From here, the application can update notifications, graphs, charts or dashboards or sequentially trigger external integrations.
+
+## The Trigger Function
+
+Trigger functions in PosgreSQL are special functions executed automatically in response to certain events on a database table. These events are basically `INSERT`, `UPDATE`, or `DELETE` which are often used to carry out business logics, audit trails and automate workflows directly at the database level.
+
+```
+CREATE OR REPLACE FUNCTION notify_on_insert() RETURNS trigger AS $$
+BEGIN
+  PERFORM pg_notify('new_invoice_created', row_to_json(NEW)::text);
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+```
+
+This sample trigger function sends a JSON payload to the `new_invoice_created` channel whenever a new record is created.
diff --git a/postgresql_notify_trigger.md b/postgresql_notify_trigger.md
@@ -0,0 +1,5 @@
+**Using PostgreSQL Notify and Trigger Functions in Ruby on Rails**
+
+One of the advanced features of PostgreSQL is the NOTIFY and LISTEN functionality. These features enable real-time communication between databases and applications. Modern web applications that requires event-driven behavoir and real-time updates will benefit from this.
+
+Combining these features with Ruby on Rails can create powerful real-time applications with minimum overhead.
No results found