Vincent Posts - Answer Overflow

Vincent

•Created by Vincent on 2/12/2025 in #help-and-questions

Silent and Stalled transaction

This is a strange one. I have a transaction running in a child process (worker). The transaction uses a temporary table to insert a bunch of ids to do an efficient join on a very large dataset. The code for this looks something like:

const remoteIdsSql = ids.map((id) => Prisma.sql`(${id})`);
const postgresDriverParamLimit = 32767;
const results = await this.db.$transaction(
  async (tx) => {
    await tx.$executeRaw`
      CREATE TEMP TABLE
        "temp_remote_ids" (
          "id" VARCHAR(200) PRIMARY KEY
        )
      ON COMMIT DROP`;

    for (const idsSqlChunk of chunkGenerator(idsSqlArray, postgresDriverParamLimit)) {
      await tx.$executeRaw`
        INSERT INTO
          "temp_remote_ids" ("id")
        VALUES
          ${Prisma.join(idsSqlChunk)}`;
    }

    const people = await tx.$queryRaw<People[]>`
      SELECT
        p.*
      FROM
        public."People" p
      INNER JOIN
        "temp_remote_ids" t ON p."id" = t."id"`;

    return people;
  },
  {
    timeout: 30000,
  },
);

const remoteIdsSql = ids.map((id) => Prisma.sql`(${id})`);
const postgresDriverParamLimit = 32767;
const results = await this.db.$transaction(
  async (tx) => {
    await tx.$executeRaw`
      CREATE TEMP TABLE
        "temp_remote_ids" (
          "id" VARCHAR(200) PRIMARY KEY
        )
      ON COMMIT DROP`;

    for (const idsSqlChunk of chunkGenerator(idsSqlArray, postgresDriverParamLimit)) {
      await tx.$executeRaw`
        INSERT INTO
          "temp_remote_ids" ("id")
        VALUES
          ${Prisma.join(idsSqlChunk)}`;
    }

    const people = await tx.$queryRaw<People[]>`
      SELECT
        p.*
      FROM
        public."People" p
      INNER JOIN
        "temp_remote_ids" t ON p."id" = t."id"`;

    return people;
  },
  {
    timeout: 30000,
  },
);

When I am running Prisma 6.0.1, I have no problems, the query runs just fine, no issues whatsoever. But if I upgrade to 6.1.0 or higher the worker stalls, and the query never completes. It is as if the transaction is failing silently. I have no errors in my logs, and my process analytics indicate the workers are still active, but no work is being completed. I have spent a lot of time trying to figure out what could possibly be going on here. I don't have a small reproduction to share, as this is a large project I am working on. My current theory is that this has something to do with Alpine 3.20, Prisma 6.0.1, and transactions. I have tried changing telemetry packages, downgrading @prisma/instrumentation, and upgrading Alpine to no avail. Any advice or even outlandish ideas would be appreciated here Thank you

14 replies

Gaming

Programming