[BUG?] Postgres transactions throwing connection timeouts after a lot of queries

I think there's a syntax error in Postgres transactions ----> see screenshot cc: @bloberenober

17 Replies

Is it postgres.js? Or node-postgres?

node-postgres

import {
  drizzle,
  NodePgQueryResultHKT,
} from "drizzle-orm/node-postgres";
import { PgDatabase } from "drizzle-orm/pg-core";
import { Pool } from "pg";

import {
  drizzle,
  NodePgQueryResultHKT,
} from "drizzle-orm/node-postgres";
import { PgDatabase } from "drizzle-orm/pg-core";
import { Pool } from "pg";

export type TxClient = PgDatabase<NodePgQueryResultHKT>;
export async function execTx<T>(
  fn: (client: TxClient) => Promise<T>
): Promise<T> {
  logger.info(`Begin Transaction`);
  const result = await db.transaction(async (tx) => fn(tx));
  logger.info(`End Transaction`);
  return result;
}

export type TxClient = PgDatabase<NodePgQueryResultHKT>;
export async function execTx<T>(
  fn: (client: TxClient) => Promise<T>
): Promise<T> {
  logger.info(`Begin Transaction`);
  const result = await db.transaction(async (tx) => fn(tx));
  logger.info(`End Transaction`);
  return result;
}

Andrii Sherman•15mo ago

https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/node-postgres/session.ts#L124

GitHub

drizzle-orm/session.ts at main · drizzle-team/drizzle-orm

TypeScript ORM for SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.

Andrii Sherman•15mo ago

Can see word “begin” Are you using latest? No syntax problems in main branch Maybe the reason in some other place? What’s exactly not working?

rushil1o1•15mo ago

Nvm that might be from a logger - but its strange because PG transactions are hanging when I use the native drizzle transaction API Drizzle ORM :

0.23.11

0.23.11

Error: timeout exceeded when trying to connect
 ❯ Timeout._onTimeout node_modules/pg-pool/index.js:200:27

Error: timeout exceeded when trying to connect
 ❯ Timeout._onTimeout node_modules/pg-pool/index.js:200:27

We're just hitting a few timeouts on lambdas that are intensive. Seed scripts, etc. that have a lot of concurrent DB calls to create multiple entities For ex: a script to seed users that creates a user object and a user-address object (FK relationship) within a transaction Running these seed scripts in parallel --- I assumed that DB transactions would automatically try to wait and coordinate access to the DB. Not sure why its hanging though

bloberenober•15mo ago

did you enable doNotWaitForEmptyLoop?

rushil1o1•15mo ago

Not that I'm aware of - is there an option to do that?

bloberenober•15mo ago

it's a Lambda option

rushil1o1•15mo ago

No - currently I'm testing this through vitest and its hanging and timing out after inserting around couple hundred rows

bloberenober•15mo ago

Are you closing the DB connection?

rushil1o1•15mo ago

Do I need to explicitly close it after the transaction runs?

rushil1o1•15mo ago

rushil1o1•15mo ago

Here's our client right now

bloberenober•15mo ago

you need to close the DB connection before you exit the app otherwise the event loop will prevent it from exiting and the app will hang

rushil1o1•15mo ago

So I did some changes and debugging: I'm now sequentially running the seed script. My max parameter in the connection pool is now set to 1 I logged the number of transactions started and ended and now it only succeeds for ONE transaction and hangs after the first transaction. Does db.transaction() not release the connection from the pool before returning out of the API?

export type TxClient = NodePgDatabase;
export async function execTx<T>(
  fn: (client: TxClient) => Promise<T>
): Promise<T> {
  let connection: PoolClient | undefined;
  try {
    connection = await pool.connect();
    const txClient = drizzle(connection, { logger: new DrizzleLogger() });
    await connection.query("BEGIN");
    const result = await fn(txClient);
    await connection.query("COMMIT");
    return result;
  } catch (err) {
    logger.error(err, "Error executing transaction");
    await connection?.query("ROLLBACK");
    throw err;
  } finally {
    connection?.release();
  }
}

export type TxClient = NodePgDatabase;
export async function execTx<T>(
  fn: (client: TxClient) => Promise<T>
): Promise<T> {
  let connection: PoolClient | undefined;
  try {
    connection = await pool.connect();
    const txClient = drizzle(connection, { logger: new DrizzleLogger() });
    await connection.query("BEGIN");
    const result = await fn(txClient);
    await connection.query("COMMIT");
    return result;
  } catch (err) {
    logger.error(err, "Error executing transaction");
    await connection?.query("ROLLBACK");
    throw err;
  } finally {
    connection?.release();
  }
}

Here's what we had before and it worked perfectly. @bloberenober I doubt this has anything to do with the event loop -- @bloberenober --- doesn't look like drizzle releases the connection at the end of the transaction.

finally {
  session.client.release();
}

finally {
  session.client.release();
}

bloberenober•15mo ago

Yup, fair I guess I misunderstood how the pools work I thought it'll reuse one of the existing connections if there is one, so no need to close it But apparently I'm wrong Will fix ...also, it seemed obvious to me that when you close a pool, it'll close all its connections, but apparently that's also not the case Tracking issue: https://github.com/drizzle-team/drizzle-orm/issues/447

rushil1o1•15mo ago

It probably does but in this case we had some many back to back transactions that it eventually ran out of connections in the pool because none of them ever closed Thanks @bloberenober !

Gaming

Programming

[BUG?] Postgres transactions throwing connection timeouts after a lot of queries