Skip to content

Queries get stuck when running static queries in concurrent cron jobs #1033

Open
@Hsin-Hung

Description

@Hsin-Hung

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions