Support passing AbortSignal in query()

[mattbishop]

Node offers an AbortSignal API that gives async operations a way to cancel their progress if aborted by the caller. Common use cases are web apps that see their request closed before completion, or in a local app, the user hits Ctrl-C to kill a running program. node-postgres's query() command could accept an AbortSignal instance and listen to it for abort events. When an abort occurs, it would close the Postgres query immediately.

A decent writeup can be found in this logrocket blog: https://blog.logrocket.com/complete-guide-abortcontroller-node-js/

Node Stream offers an option to pass an AbortSignal instance to close streams when an abort occurs. https://nodejs.org/dist/latest-v18.x/docs/api/all.html#all_stream_streamaddabortsignalsignal-stream

[brianc]

That would be a pretty nice way to cancel a query. Canceling a query with postgres is kinda a gross operation under the hood which is why support isn't great in this library. You have to create a new connection and then tell postgres to cancel a query from another connection - the active connection running the query is "unresponsive" from our app's perspective while its waiting for a query to execute, and the backend doesn't read any network data once it receives a command to execute a query. It doesn't even check the socket to make sure it's still connected (which is why if your process dies and you're running a 30 min query it will still run in postgres). So while DX the semantics of wanting to kill a stream and have it tell postgres to cancel the query are really nice - what would need to happen under the hood is actually spinning an entirely new connection to the backend, going through the connection handshake, and then telling the backend to cancel the other connection's query. Then the existing query will throw an error saying 'terminiated by backend.' It's a whole bunch of chaos. I do want to get better support for canceling queries in the near future though because yah killing your program and having a rouge query running for 30 min in the background without any way to stop it outside of going to psql and finding and kiling it is annoying.

[mattbishop]

Wow I had no idea it was so complicated!

I guess you can’t just kill the tcp connection on abort?

— Matt Bishop

On Jul 20, 2022, at 11:26 AM, Brian C @.***> wrote:

 That would be a pretty nice way to cancel a query. Canceling a query with postgres is kinda a gross operation under the hood which is why support isn't great in this library. You have to create a new connection and then tell postgres to cancel a query from another connection - the active connection running the query is "unresponsive" from our app's perspective while its waiting for a query to execute, and the backend doesn't read any network data once it receives a command to execute a query. It doesn't even check the socket to make sure it's still connected (which is why if your process dies and you're running a 30 min query it will still run in postgres). So while DX the semantics of wanting to kill a stream and have it tell postgres to cancel the query are really nice - what would need to happen under the hood is actually spinning an entirely new connection to the backend, going through the connection handshake, and then telling the backend to cancel the other connection's query. Then the existing query will throw an error saying 'terminiated by backend.' It's a whole bunch of chaos. I do want to get better support for canceling queries in the near future though because yah killing your program and having a rouge query running for 30 min in the background without any way to stop it outside of going to psql and finding and kiling it is annoying.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.