I'm currently working on a feature in our TypeORM and PostgreSQL setup where I need to handle cancellation of long-running and time-consuming requests. For this, I believe I need to track the Process ID (PID) of specific PostgreSQL connections to properly cancel queries that have been requested to stop. Does anyone have experience or know how to retrieve the PID for a query in TypeORM?
I'm considering using a custom query to call pg_backend_pid(), but I'm wondering if there's a more efficient way or standard practice for doing this, especially in a production environment. Any insights on managing this kind of query cancellation would be greatly appreciated!
There is this open issue in the TypeORM github so this is most likely not implemented in any way: https://github.com/typeorm/typeorm/issues/8552
I think using pg_backend_pid() and pg_cancel_backend() is your best bet, you just have to make sure to run your query and pg_backend_pid() on the same connection.
How I would go about it:
// Connection to run queries
// DB is your TypeORM DataSource
const qR = DB.createQueryRunner();
// Get the pid of the connection which you will use to run your query
const [{connection_pid}] = await qR.query('SELECT pg_backend_pid() AS connection_pid');
// Assuming desired timeout for a query is 30s
// The query cancellation uses `DB.query` instead of `qR.query` to run on a different connection
const timeoutId = setTimeout(() => DB.query(`SELECT pg_cancel_backend(${connection_pid})`), 30_000);
// Run your query
const queryResult = await qR.query('.....');
// If the query completes within 30s the timeout gets cleared so it won't stop any queries in the future
// If the query didn't complete in time the timeout cancels it
clearTimeout(timeoutId);
// Release connection back into the pool
qR.release();