Pool’s `prepare()` method leaks?

[maximal]

Hi there!

I’m trying to build some kind of an authorization middleware and faced an issue where the server runs out of memory after a constant count of requests (depending on PHP’s memory_limit setting), when using PostgresConnectionPool::prepare(). When using raw query() directly, everything works fine.

My middleware is:

<?php

namespace App\Http\Middlewares;

use Amp\Http\HttpStatus;
use Amp\Http\Server\Middleware;
use Amp\Http\Server\Request;
use Amp\Http\Server\RequestHandler;
use Amp\Http\Server\Response;
use Amp\Postgres\PostgresConfig;
use Amp\Postgres\PostgresConnectionPool;

final readonly class AuthMiddleware implements Middleware
{
	private PostgresConnectionPool $pool;

	public function __construct()
	{
		$this->pool = new PostgresConnectionPool(
			PostgresConfig::fromString('host=127.0.0.1 port=5431 user=user password=password db=database')
		);
		// echo 'Auth middleware initialized.', PHP_EOL;
	}

	public function handleRequest(Request $request, RequestHandler $requestHandler): Response
	{
		$authorization = $request->getHeader('Authorization');

		// ...
		// ... getting the token from `Authorization` header ...
		// ...

		// region `prepare()->execute()` variant
		// `prepare()` leaks?
		//$statement = $this->pool->prepare(
		//	'SELECT
		//		u.*,
		//		pat.expires_at AS access_token_expires_at
		//	FROM personal_access_tokens pat
		//		INNER JOIN users u ON u.id = pat.tokenable_id
		//	WHERE pat.tokenable_type = :type AND pat.token = :hash AND pat.expires_at > now()'
		//);
		//$result = $statement->execute([
		//	'type' => 'user',
		//	'hash' => hash('sha256', $tokenText),
		//]);
		//$statement->close();
		// endregion `prepare()->execute()` variant

		// region `query()` variant
		// `query()` variant works just fine, but using “ugly” parameter setting
		$result = $this->pool->query(
			sprintf(
				'SELECT
					u.*,
					pat.expires_at AS access_token_expires_at
				FROM personal_access_tokens pat
					INNER JOIN users u ON u.id = pat.tokenable_id
				WHERE pat.tokenable_type = \'%s\' AND pat.token = \'%s\' AND pat.expires_at > now()',
				'user',
				hash('sha256', $tokenText)
			)
		);
		// endregion `query()` variant

		$user = $result->fetchRow();
		if ($user === null) {
			return new Response(HttpStatus::FORBIDDEN, [], 'Forbidden');
		}

		// Authorize the user
		unset($user['password']);
		$request->setAttribute('user', (object)$user);
		unset($user);

		return $requestHandler->handleRequest($request);
	}
}

The exception is the classic one:

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /path/to/project/vendor/amphp/sql-common/src/SqlPooledResult.php on line 52
// or
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /path/to/project/vendor/amphp/sql-common/src/SqlStatementPool.php on line 64
// or other file and line

Is it a problem inside of AMPHP’s database library, or am I using it wrong?

[trowski]

Hi @maximal,

Thanks for reporting this! I found this was due to SqlStatementPool accumulating on-close callbacks in SqlCommonConnectionPool. I fixed this using a WeakMap to avoid attaching a callback to the onClose future each time a statement pool is created.

This has been tagged as amphp/sql-common@v2.0.2. Please update your dependency and the memory leak should be fixed!

[trowski]

One quick note: You optionally can choose to cache the prepared statement object in your request handler to avoid preparing the statement on each request.

[maximal]

You optionally can choose to cache the prepared statement object in your request handler to avoid preparing the statement on each request.

@trowski, you mean, manually using amphp/cache, or do statements have internal functionality for this?