Recommended way to work with Postgres listen/notify #3001
Unanswered
owenthereal
asked this question in
Q&A
Replies: 1 comment
-
Hello, it's possible using First you will need a way to create the triggers and call See the example below: function CREATE OR REPLACE FUNCTION notify_table_events_fn() RETURNS TRIGGER AS
$$
DECLARE
data json;
notification json;
BEGIN
-- FROM: https://coussej.github.io/2015/09/15/Listening-to-generic-JSON-notifications-from-PostgreSQL-in-Go/
-- Convert the old or new row to JSON, based on the kind of action.
-- Action = DELETE? -> OLD row
-- Action = INSERT or UPDATE? -> NEW row
IF (TG_OP = 'DELETE') THEN
data = row_to_json(OLD);
ELSE
data = row_to_json(NEW);
END IF;
-- Contruct the notification as a JSON string.
notification = json_build_object(
'table', TG_TABLE_NAME,
'action', TG_OP,
'data', data);
-- Execute pg_notify(channel, notification)
PERFORM pg_notify('table_events', notification::text);
-- Result is ignored since this is an AFTER trigger
RETURN NULL;
END;
$$ LANGUAGE plpgsql; triggers CREATE TRIGGER notify_tenant_events_trigger
AFTER INSERT OR UPDATE OR DELETE
ON global_tenants
FOR EACH ROW
EXECUTE PROCEDURE notify_table_events_fn();
CREATE TRIGGER notify_tenant_events_trigger
AFTER INSERT OR UPDATE OR DELETE
ON global_access_control
FOR EACH ROW
EXECUTE PROCEDURE notify_table_events_fn(); We need get a reference to the database as well, we could implement using custom templates or just adding one file to Ent folder with the content: // DB returns the inner *sql.DB from ent.
func (c *Client) DB() *sql.DB {
drv := c.driver
debugDrv, ok := c.driver.(*dialect.DebugDriver)
if ok {
drv = debugDrv.Driver
}
return drv.(*entsql.Driver).DB()
} Go code func (s *Server) startTableChangesListener(ctx context.Context) error {
pgxConn, err := stdlib.AcquireConn(s.entClientGlobal.DB())
if err != nil {
// handle error
}
defer func() {
if err := stdlib.ReleaseConn(s.entClientGlobal.DB(), pgxConn); err != nil {
// handle error
}
}()
_, err = pgxConn.Exec(ctx, "listen table_events")
if err != nil {
// handle error
}
for {
notification, err := pgxConn.WaitForNotification(ctx)
if err != nil {
// handle error
}
var event tableChangesNotification
err = json.Unmarshal([]byte(notification.Payload), &event)
if err != nil {
// handle error
}
// handle event here
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hello,
What would be the recommended way to use Postgres's listen/notify in ent? It would be nice to have some examples.
Thanks!
Beta Was this translation helpful? Give feedback.
All reactions