Transaction leaking after pg pool patch

[Weiko]

DB connection examples

// Instantiate low level query runner
const workspaceDataSource = await this.twentyORMGlobalManager.getDataSourceForWorkspace({
	workspaceId: this.getWorkspaceId(),
});

const queryRunner = workspaceDataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();
await queryRunner.query("SELECT *...")
await queryRunner.commitTransaction();
...

// Instantiate high level repository
const workspaceDataSource = await this.twentyORMGlobalManager.getDataSourceForWorkspace({
	workspaceId: this.getWorkspaceId(),
});
const repository = workspaceDataSource.getRepository(
	objectMetadataItemWithFieldMaps.nameSingular,
	shouldBypassPermissionChecks,
	roleId,
	authContext,
);
const records = repository.find({...});

Both need a connection to the DB, provided by the datasource.

Workspace datasource dynamic instantiation

in workspace-datasource.factory.ts

public async create(workspaceId: string): Promise<WorkspaceDataSource> {
  const cacheKey: CacheKey = `${workspaceId}-${dataSourceMetadataVersion}`;

  const workspaceDataSource = ...
     new WorkspaceDataSource(
       ...
	    url: dataSourceMetadata.url ?? this.twentyConfigService.get('PG_DATABASE_URL'),
		type: 'postgres',
		logging: this.twentyConfigService.getLoggingConfig(),
		schema: dataSourceMetadata.schema,
		entities: this.entitySchemaFactory.create(
			workspaceId,
			dataSourceMetadataVersion,
			objectMetadata,
			cachedObjectMetadataMaps,
		),
	)
}

Each datasource manages its own pool of connection.

	// Typeorm first instruction when we use the datasource
	await dataSource.initialize()

	// Initialize calls its driver "connect" method, in our case PostgresDriver
	// see https://github.com/typeorm/typeorm/blob/master/src/driver/postgres/PostgresDriver.ts#L346
	// Which creates a pool
	await this.createPool(...)

which is an issue in our multi-tenant arch where each workspace has its own datasources because 1000 datasources would mean 1000 pools (with each pool having a bunch of idle connections in them) so we needed to patch that behavior.

PgPool patch (04/06/25)

in pg-shared-pool.service.ts

// Replace the original Pool with our patched version
Object.defineProperty(pgWithSymbol, 'Pool', {
  value: SharedPool as unknown as typeof Pool,
  writable: true,
  configurable: true,
  enumerable: this.originalPgPoolDescriptor?.enumerable,
});

...
// Patch where we use cache to reuse the same pool accross the different datasources
const key = buildPoolKey(poolConfig);
const existing = poolsMap.get(key);

if (existing) {
  if (isDebugEnabled) {
    logger.debug(`Reusing existing pg Pool for key "${key}"`);
  }

  return existing;
}

Typeorm using the pool internally

// See https://github.com/typeorm/typeorm/blob/master/src/driver/postgres/PostgresQueryRunner.ts#L254
// This low-level method is used pretty much everywhere within typeorm AFAIK
async query(
	query: string,
	parameters?: any[],
	useStructuredResult: boolean = false,
): Promise<any> {
	const databaseConnection = await this.connect() // Asking the pool for a connection
	const raw = await databaseConnection.query(query, parameters) // Running the query within the pool
}

Issue

https://node-postgres.com/apis/pool see

Do not use pool.query if you are using a transaction.
The pool will dispatch every query passed to pool.query on the first available idle client. Transactions within PostgreSQL are scoped to a single client and so dispatching individual queries within a single transaction across multiple, random clients will cause big problems in your app and not work. For more info please read transactions .

---

As explained above, each individual query will run through the pool of connections and a transaction can be started and commit in different connections. The more we have ongoing requests, the more likely this can happen and some transactions can either:

• be commit earlier than it should and corrupt the data
• never receive commit and hang in "idle in transaction" state and locking the DB.

Mitigation strategy

In the meantime, we should avoid transactions when it's possible, including the ones started by TypeORM.
This is the case for the save method that runs.

• SELECT (for event broadcasting),
• BEGIN TRANSACTION,
• INSERT INTO / UPDATE,
• COMMIT TRANSACTION,
• SELECT

Long term strategy

Later on, we should remove our pg-pool patch. We should instead patch typeorm datasource to work with our multi-tenant and transaction needs (see SAVEPOINT: https://www.postgresql.org/docs/current/sql-savepoint.html if needed)