N
Neon•2y ago
wise-white

does neon tech support pg notify?

does neon tech support pg notify?
6 Replies
genetic-orange
genetic-orange•2y ago
Yes!
No description
genetic-orange
genetic-orange•2y ago
I just tested the whole system. So here are the steps 🧵 1. Create a Neon database. Go to the SQL Editor and enter 
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    message TEXT
);

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('channel_name', NEW.message);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_trigger
AFTER INSERT ON test_table
FOR EACH ROW
EXECUTE FUNCTION notify_trigger();
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    message TEXT
);

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('channel_name', NEW.message);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_trigger
AFTER INSERT ON test_table
FOR EACH ROW
EXECUTE FUNCTION notify_trigger();
genetic-orange
genetic-orange•2y ago
You'd see this.
No description
genetic-orange
genetic-orange•2y ago
2. Create a listener script: 
const { Client } = require("pg");

const connectionString =
  "postgresql://neondb_owner:...@....us-east-2.aws.neon.tech/neondb?sslmode=require";

const client = new Client({
  connectionString,
});

async function setup() {
  await client.connect();
  client.on("notification", (msg) => {
    console.log("Notification received:", msg.payload);
  });
  await client.query("LISTEN channel_name");
  console.log("Listening for notifications on channel_name");
}

setup().catch((err) => {
  console.error("Error setting up the listener", err);
  process.exit(1);
});
const { Client } = require("pg");

const connectionString =
  "postgresql://neondb_owner:...@....us-east-2.aws.neon.tech/neondb?sslmode=require";

const client = new Client({
  connectionString,
});

async function setup() {
  await client.connect();
  client.on("notification", (msg) => {
    console.log("Notification received:", msg.payload);
  });
  await client.query("LISTEN channel_name");
  console.log("Listening for notifications on channel_name");
}

setup().catch((err) => {
  console.error("Error setting up the listener", err);
  process.exit(1);
});
and run this via 
node listen.js
node listen.js
3. Create a send notification script: 
const { Client } = require("pg");

const connectionString =
  "postgresql://neondb_owner:...@....us-east-2.aws.neon.tech/neondb?sslmode=require";

const client = new Client({
  connectionString,
});

async function sendNotification(message) {
  await client.connect();
  await client.query("INSERT INTO test_table (message) VALUES ($1)", [message]);
  await client.end();
}

sendNotification("Hello, World!").catch((err) => {
  console.error("Error sending notification", err);
  process.exit(1);
});
const { Client } = require("pg");

const connectionString =
  "postgresql://neondb_owner:...@....us-east-2.aws.neon.tech/neondb?sslmode=require";

const client = new Client({
  connectionString,
});

async function sendNotification(message) {
  await client.connect();
  await client.query("INSERT INTO test_table (message) VALUES ($1)", [message]);
  await client.end();
}

sendNotification("Hello, World!").catch((err) => {
  console.error("Error sending notification", err);
  process.exit(1);
});
and run this via 
node send.js
node send.js
and you should see as in my first screenshot.
ambitious-aqua
ambitious-aqua•2y ago
Oh cool! I’m using rust and sqlx, I think it has support as well thanks you! Do you think this would be a suitable replacement for using websockets? Like what is an ideal use case for this?
xenogeneic-maroon
xenogeneic-maroon•2y ago
Oh cool! I’m using rust and sqlx, I think it has support as well thanks you!
Yes, absolutely
Like what is an ideal use case for this?
Listen/Notify is great for work queue use cases. You can insert an entry into a table and notify another postgres session that this entry is available. That session could then claim this row as a 'inflight work'' Someone else designed this implementation. I gave some more details in the reply to this message https://discord.com/channels/1176467419317940276/1178738361276563546/1241660433539141722

Did you find this page helpful?