[Doctrine Messenger] Fix support for pgsql + pgbouncer. #53819

jwage · 2024-02-07T02:11:26Z

Q	A
Branch?	6.4
Bug fix?	yes
New feature?	no
Deprecations?	no
Issues
License	MIT

Problem

When you use PgBouncer in front of a PostgreSQL server with transaction pooling mode enabled, the INSERT and the lastInsertId() happen in separate transactions which are separate connections/sessions when using PgBouncer. So the call to lastInsertId() fails with the following exception:

[Doctrine\DBAL\Exception\DriverException (7)]
An exception occurred in the driver: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  lastval is not yet defined in this session

Exception trace:
 at /app/vendor/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:87
Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert() at /app/vendor/doctrine/dbal/src/Connection.php:1938
Doctrine\DBAL\Connection->handleDriverException() at /app/vendor/doctrine/dbal/src/Connection.php:1886
Doctrine\DBAL\Connection->convertException() at /app/vendor/doctrine/dbal/src/Connection.php:1253
Doctrine\DBAL\Connection->lastInsertId() at /app/vendor/symfony/doctrine-messenger/Transport/Connection.php:156
Symfony\Component\Messenger\Bridge\Doctrine\Transport\Connection->send() at /app/vendor/symfony/doctrine-messenger/Transport/DoctrineSender.php:46
Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineSender->send() at /app/vendor/symfony/doctrine-messenger/Transport/DoctrineTransport.php:72
Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineTransport->send() at /app/vendor/symfony/messenger/EventListener/SendFailedMessageForRetryListener.php:81
Symfony\Component\Messenger\EventListener\SendFailedMessageForRetryListener->onMessageFailed() at /app/vendor/symfony/event-dispatcher/EventDispatcher.php:220
Symfony\Component\EventDispatcher\EventDispatcher->callListeners() at /app/vendor/symfony/event-dispatcher/EventDispatcher.php:56
Symfony\Component\EventDispatcher\EventDispatcher->dispatch() at /app/vendor/symfony/messenger/Worker.php:198
Symfony\Component\Messenger\Worker->ack() at /app/vendor/symfony/messenger/Worker.php:174
Symfony\Component\Messenger\Worker->handleMessage() at /app/vendor/symfony/messenger/Worker.php:109

Solution

Wrap the INSERT and lastInsertId() with a single transaction, then when lastInsertId() is called, it will be within the same session that the message was inserted in.

In addition, this PR adds the ability to use PostgresSQL RETURNING id clause instead of calling lastInsertId() so we can get the id of the inserted message in one operation instead of two.

TODO:

Add test for table not found scenario when inserting a message.
Add tests for when lastInsertId returns false, int and string.
Is there a place where I can write an integration test for this behavior that already exists?
Investigate using pgsql RETURNING clause to simplify this. The insert can return the id after the message is inserted.
Squash commits to one clean commit before merge.

jwage · 2024-02-07T02:21:56Z

I need to work on this some more. Tests are failing.

jwage · 2024-02-07T13:17:52Z

I don't think these test failures are related to my changes.