trying to connect with psql from win 10
trying to connect with psql from win 10 box, but getting a connection error. all I do is, run "psql postgresql://neondb_owner:GBPr1epQY8Cg@ep-white-dew-a6toqdmj.us-west-2.aws.neon.tech/neondb?sslmode=require" from a cmd line prompt. what am I missing?
14 Replies
optimistic-gold•13mo ago
What error do you see?
Also, the connection string is sensitive. I would make sure to reset the password
national-gold•13mo ago
all it said was "connection error", but now it works. Maybe the pw wasn't visible (i.e. *'s)..
Yeah I'll reset it - nothing in it yet..
optimistic-gold•13mo ago
Awesome! Glad that things are working now. Feel free to reach out if you run into any issues or if you have any questions
national-gold•13mo ago
One question I have is how to set up the db to provide an alert (to a webSocket connection) when there is a change to a specific table?
optimistic-gold•13mo ago
Do you mind sharing more details about your use case?
I'd either look into using LISTEN/NOTIFY or triggers
national-gold•13mo ago
Users would submit jobs on an infrequent basis. Through a web UI. When a job has been submitted (and recorded in the db - in a jobs table), a jobs engine would execute the job. So, the jobs engine needs to be notified when there is a job to run. Or, the jobs engine needs to query the db frequently (like 1 time per second) to know when a job has been submitted, and it needs to kick it off. It seems more logical for the jobs engine to wait for a notification vs continuously querying the db for a change.
optimistic-gold•13mo ago
So I think what you would need to do is:
1. Set up a database trigger on the jobs table
2. Implement a message queue system (optional, for added reliability)
3. Create a WebSocket server
4. The jobs engine client that connects to the WebSocket server
5. Modify the web UI to insert jobs into the database
6. Ensure the WebSocket server is running and listening for database notifications
7. Configure the jobs engine to process jobs upon receiving notifications
national-gold•13mo ago
so, the websocket server is a long running connection that stays connected to the db, and the only communication is the db's trigger notification, which is pushed from the db?
optimistic-gold•13mo ago
Yes
national-gold•13mo ago
I've been trying to research how to set up a trigger and the end-point to notify. I assume this is using the TCN extension? But I can't find any docs on how to specify where the notification is to be sent. Is there any documentation you can point me to?
unwilling-turquoise•13mo ago
Hey @Anders -- we just launched an open source project that can help you do this: Sequin (https://github.com/sequinstream/sequin). Give it a try and feel free to DM me if you need anything
national-gold•13mo ago
I'm able to connect and listen with '@neondatabase/serverless', but I only receive the messages when I don't use the "pooler". But w/o the "pooler" I only stay connected for 5 minutes, which is a problem.
import { Pool, neonConfig} from '@neondatabase/serverless'
import ws from "ws"
import * as env from "dotenv"
env.config()
neonConfig.webSocketConstructor = ws
let pool = new Pool({ connectionString: process.env.DBURL })
console.log(
Started at ${new Date().toLocaleString()})
const client = await pool.connect()
await client.query('LISTEN tcn')
client.on('notification', (msg)=>showMSG(msg))
function showMSG(msg) {
console.log(At ${new Date().toLocaleString()} -- ${msg.channel}: ${msg.payload})
}
I've got it working, sort of. But could use more help. With the serverless driver and no pooler I can receive messages. The problem is that I don't stay connected w/o the pooler.optimistic-gold•13mo ago
Are you on the free tier? This could be related to the fact that your database shuts down after 5 mins of inactivity
national-gold•13mo ago
I am on the free tier. For now. But I think I found a way that works. Using the Postgres driver, not PG. And the sql.listen method. No WS needed and it remains connected. 12 hrs tested so far. Any change to the table is immediately reported. Also, I used the pooler connection string.