How Can I Fire a Long-Running SQL Query and Close the DB Connection Immediately?

The behavior you’re experiencing is expected: PostgreSQL rolls back any open transaction when the connection is closed, so simply disconnecting with the client will cancel the work in progress. In other words, there isn’t a “fire-and-forget” mode that lets you disconnect immediately while allowing the query to fully run on the backend.

Here are a few alternatives to consider:

1. Use a Background Worker or Job Scheduler

Instead of trying to run the long query directly from your request, consider offloading it to a background process. For example:

• pg_cron or pgAgent: These PostgreSQL extensions let you schedule jobs.
• Queue-based approach: Have your application enqueue the work (e.g., using RabbitMQ, Bull, etc.) and let a dedicated worker process execute the query.

This way, your web request can return immediately while the background process handles the long-running work.

2. Use a Dedicated Process/Connection for Long Queries

If you really need to “fire and forget” from PostgreSQL directly, you can use an intermediary process or stored procedure that:

• Opens its own connection,
• Fires the query, and
• Disconnects only after the query is done.

For instance, you could create a stored procedure that calls your function asynchronously (using something like the pg_background extension). Your application would then call the procedure, and the procedure would schedule the work without tying up your client connection.

3. Use dblink (With Caution)

Another approach is to use the dblink extension to issue the long-running query from within a SQL function. The dblink call can be made without waiting for the result and in a separate connection context. For example:

SQLCopied
SELECT dblink_connect('background', 'dbname=mydb user=myuser password=mypass host=myhost');
SELECT dblink_exec('background', 'SELECT refresh_mv(''2023-10-01'')');
SELECT dblink_disconnect('background');

This sends the query off on a separate connection. However, note that even with dblink, PostgreSQL doesn’t provide a true “fire-and-forget” mechanism—the query is still subject to the server’s usual transaction and statement timeout rules unless you design the function accordingly.

Summary

• Simply closing the connection cancels any active query.
• Offload long queries to a background worker or scheduled job.
• Using extensions like pg_background or dblink may help, but they require extra configuration and come with their own caveats.

By designing your system to separate the query initiation from its execution, you avoid tying up a client connection and prevent timeouts on the client side.