Rails & postgresql, notify/listen to when a new record is created

Question

I'm experimenting & learning how to work with PostgreSQL, namely its Notify/Listen feature, in the context of making Server-Sent Events according to this tutorial.

The tutorial publishes NOTIFY to the user channel (via its id) whenever a user is saved and an attribute, authy_status is changed. The LISTEN method then yields the new authy_status Code:

class Order < ActiveRecord::Base
after_commit :notify_creation

def notify_creation
  if created?
    ActiveRecord::Base.connection_pool.with_connection do |connection|
        execute_query(connection, ["NOTIFY user_?, ?", id, authy_status])
    end
  end
end

def on_creation
  ActiveRecord::Base.connection_pool.with_connection do |connection|
    begin
      execute_query(connection, ["LISTEN user_?", id])
      connection.raw_connection.wait_for_notify do |event, pid, status|
        yield status
      end
    ensure
      execute_query(connection, ["UNLISTEN user_?", id])
    end
  end
end

end

I would like to do something different, but haven't been able to find information on how to do this. I would like to NOTIFY when a user is created in the first place (i.e., inserted into the database), and then in the LISTEN, I'd like to yield up the newly created user itself (or rather its id).

How would I modify the code to achieve this? I'm really new to writing SQL so for example, I'm not very sure about how to change ["NOTIFY user_?, ?", id, authy_status] to a statement that looks not at a specific user, but the entire USER table, listening for new records (something like... ["NOTIFY USER on INSERT", id] ?? )

CLARIFICATIONS

Sorry about not being clear. The after_save was a copy error, have corrected to after_commit above. That's not the issue though. The issue is that the listener listens to changes in a SPECIFIC existing user, and the notifier notifies on changes to a SPECIFIC user.

I instead want to listen for any NEW user creation, and therefore notify of that. How does the Notify and Listen code need to change to meet this requirement?

I suppose, unlike my guess at the code, the notify code may not need to change, since notifying on an id when it's created seems to make sense still (but again, I don't know, feel free to correct me). However, how do you listen to the entire table, not a particular record, because again I don't have an existing record to listen to?

For broader context, this is the how the listener is used in the SSE in the controller from the original tutorial:

  def one_touch_status_live
    response.headers['Content-Type'] = 'text/event-stream'
    @user = User.find(session[:pre_2fa_auth_user_id])
    sse = SSE.new(response.stream, event: "authy_status")
    begin
      @user.on_creation do |status|
        if status == "approved"
          session[:user_id] = @user.id
          session[:pre_2fa_auth_user_id] = nil
        end
        sse.write({status: status})
      end
    rescue ClientDisconnected
    ensure
      sse.close
    end
  end

But again, in my case, this doesn't work, I don't have a specific @user I'm listening to, I want the SSE to fire when any user has been created... Perhaps it's this controller code that also needs to be modified? But this is where I'm very unclear. If I have something like...

User.on_creation do |u|

A class method makes sense, but again how do I get the listen code to listen to the entire table?

Answer 1

Please use after_commit instead of after_save. This way, the user record is surely committed in the database

There are two additional callbacks that are triggered by the completion of a database transaction: after_commit and after_rollback. These callbacks are very similar to the after_save callback except that they don't execute until after database changes have either been committed or rolled back.

https://guides.rubyonrails.org/active_record_callbacks.html#transaction-callbacks

Actually it's not relevant to your question, you can use either.

Here's how I would approach your use case: You want to get notified when an user is created:

#app/models/user.rb
class User < ActiveRecord::Base
  after_commit :notify_creation

  def notify_creation
    if id_previously_changed?
      ActiveRecord::Base.connection_pool.with_connection do |connection|
        self.class.execute_query(connection, ["NOTIFY user_created, '?'", id])
      end
    end
  end

  def self.on_creation
    ActiveRecord::Base.connection_pool.with_connection do |connection|
      begin
        execute_query(connection, ["LISTEN user_created"])
        connection.raw_connection.wait_for_notify do |event, pid, id|
          yield self.find id
        end
      ensure
        execute_query(connection, ["UNLISTEN user_created"])
      end
    end
  end

  def self.clean_sql(query)
    sanitize_sql(query)
  end

  def self.execute_query(connection, query)
    sql = self.clean_sql(query)
    connection.execute(sql)
  end
end

So that if you use

User.on_creation do |user|
  #do something with the user
  #check user.authy_status or whatever attribute you want.
end

One thing I am not sure why you want to do this, because it could have a race condition situation where 2 users being created and the unwanted one finished first.