[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
Andrii Sherman
Andrii Sherman16mo ago
Is it postgres.js? Or node-postgres?
rushil1o1
rushil1o116mo ago
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
Andrii Sherman16mo ago
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
Andrii Sherman16mo 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
rushil1o116mo 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
bloberenober16mo ago
did you enable doNotWaitForEmptyLoop?
rushil1o1
rushil1o116mo ago
Not that I'm aware of - is there an option to do that?
bloberenober
bloberenober16mo ago
it's a Lambda option
rushil1o1
rushil1o116mo ago
No - currently I'm testing this through vitest and its hanging and timing out after inserting around couple hundred rows
bloberenober
bloberenober16mo ago
Are you closing the DB connection?
rushil1o1
rushil1o116mo ago
Do I need to explicitly close it after the transaction runs?
rushil1o1
rushil1o116mo ago
rushil1o1
rushil1o116mo ago
Here's our client right now
bloberenober
bloberenober16mo 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
rushil1o116mo 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
bloberenober16mo 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
rushil1o116mo 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 !