[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•2y 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•2y 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?

rushil1o1OP•2y 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

Dan•2y ago

did you enable doNotWaitForEmptyLoop?

rushil1o1OP•2y ago

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

Dan•2y ago

it's a Lambda option

rushil1o1OP•2y ago

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

Dan•2y ago

Are you closing the DB connection?

rushil1o1OP•2y ago

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

rushil1o1OP•2y ago

rushil1o1OP•2y ago

Here's our client right now

Dan•2y 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

rushil1o1OP•2y 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();
}

Dan•2y 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

rushil1o1OP•2y 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