Description
When running concurrent queries inside cron jobs, queries get stuck indefinitely if one of the running queries is a static query (i.e., not parameterized).
I set the max
connection to 1 to reproduce the issue quickly, but even with the default max = 10
, the queries still eventually get stuck.
All the connections appear to be stuck in the full
queue, which causes new queries to pile up in the queries
queue.
Environment
- node version: v22.12.0
- database: Supabase with transaction pooling (port:
6543
) - postgres.js version: v3.4.5
Steps to Reproduce
Code to Reproduce the Issue:
const express = require("express");
const { CronJob } = require("cron");
const postgres = require("postgres");
require("dotenv").config();
const app = express();
const PORT = 3000;
const sql = postgres({
host: process.env.DATABASE_HOST,
port: process.env.DATABASE_PORT,
database: "postgres",
username: process.env.DATABASE_USERNAME,
password: process.env.DATABASE_PASSWORD,
prepare: false,
max: 1,
});
new CronJob("*/1 * * * * *", async () => {
try {
console.log("[Cron 1] Update User");
// static query (causes stuck query)
const data = await sql`
UPDATE users
SET name = 'Henry'
WHERE id = 'f35e9116-eeda-47d9-9b5e-8444a057a919'
RETURNING *
`;
console.log(`[Cron 1] Updated User Data: ${data.length}`);
} catch (error) {
console.error(error);
}
}, null, true, null, null, false, null, false, true);
new CronJob("*/1 * * * * *", async () => {
try {
console.log("[Cron 2] Update User");
// parameterized query
const data = await sql`
UPDATE users
SET name = 'Henry'
WHERE id = ${"f35e9116-eeda-47d9-9b5e-8444a057a919"}
RETURNING *
`;
console.log(`[Cron 2] Updated User Data: ${data.length}`);
} catch (error) {
console.error(error);
}
}, null, true, null, null, false, null, false, true);
app.listen(PORT, () => {
console.log(`Server is running on http://localhost:${PORT}`);
});
Running the above code causes the queries to hang, producing output similar to the example below. The query appears to be stuck in ClientRead
. However, once you parameterize the first query's WHERE id = 'f35e9116-eeda-47d9-9b5e-8444a057a919'
by changing it to WHERE id = ${"f35e9116-eeda-47d9-9b5e-8444a057a919"}
, it runs fine.
Server is running on http://localhost:3000
[Cron 1] Update User
[Cron 2] Update User
[Cron 1] Updated User Data: 1
[Cron 2] Updated User Data: 1
[Cron 1] Update User
[Cron 2] Update User
[Cron 1] Updated User Data: 1
[Cron 2] Updated User Data: 1
[Cron 1] Update User
[Cron 2] Update User
[Cron 1] Updated User Data: 1
[Cron 1] Update User