Prisma•3w ago

Postgres INSERTs Reappearing After Termination — Advice?

Hey folks 👋 — I'm running into an issue with some INSERT INTO queries that keep showing up in pg_stat_activity as "active", even after I attempt to kill their backend PIDs using pg_terminate_backend(). I'm specifically targeting INSERT INTO operations on a particular table (let's call it KeywordInsight). I successfully terminate the processes (they return true), but when I immediately check again, the same PIDs show up as still "active", still running similar insert statements. This keeps looping and I'm unable to fully clear them out. Has anyone seen this behavior before? Could it be: A retry loop from an external app? A bug in how I'm querying/killing sessions? Postgres not fully terminating the backend yet? Any advice on what might be causing persistent, zombie-like INSERTs would be hugely appreciated 🙏

7 Replies

Prisma AI Help•3w ago

You've gone full bot mode! Let me fetch that AI answer for you ASAP. This behavior—where INSERT queries keep reappearing as "active" in pg_stat_activity even after terminating their backend PIDs—can be caused by a few things, but based on the available Prisma knowledge sources, here are some relevant considerations: 1. External Retry Loops: If your application or an external service is configured to automatically retry failed queries, killing the backend process will only temporarily stop the query. The application will immediately reconnect and re-issue the INSERT, resulting in new backend PIDs showing up with similar queries. This is a common pattern in many ORMs and connection libraries, including Prisma, especially if error handling or retry middleware is in place. For example, one user mentioned planning to use retry middleware to handle intermittent connection issues with Prisma, which could lead to repeated query attempts if not managed carefully Error in PostgreSQL connection. 2. Application Not Disconnecting Properly: If your application does not gracefully close Prisma Client connections (e.g., not calling prisma.$disconnect()), connections may linger or be re-established by the client, especially in serverless or containerized environments. This can result in repeated or "zombie" queries if the application logic keeps retrying after termination Issue with not closing DB connections. 3. Postgres Backend Termination Delay: While pg_terminate_backend() should kill the process, there can be a short delay before Postgres fully cleans up the session, especially if the client immediately reconnects and re-issues the query. Advice: - Check your application logs for retry logic or error handling that might be re-issuing the INSERTs. - Ensure you are properly disconnecting Prisma clients after use. - If using serverless or autoscaling environments, ensure old instances are not still running and reconnecting. - Consider lowering Postgres connection timeouts to help clean up idle or stuck connections more quickly deleteMany call results in idle_in_transaction process. If the issue persists, you may need to audit your application code and deployment environment for unintended retry loops or improper connection handling. 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.