thanks for the input on doctrine slack @sbuerk

for symfony, i think we need to extend PostgreSQLPlatform and overwrite getCreateTableSQL with a custom platform that adds the notify sql bit. and provide a CustomPlatformDriverMiddleware and CustomPlatformDriverDecorator similar to typo3.

however, where to put that code? it probably should be in doctrine-bridge. but then, do we change doctrine/doctrine-bundle to load our middleware?

and having the logic about messenger mixed into the doctrine code seems highly specific. should we re-introduce some symfony event that the messenger can listen to? or some alternative mechanism to events to handle this?

Maybe Doctrine DBAL misses a way to inject custom logic in this schema management in a composition-based extension point. Having to extend the platform class is OK for projects (assuming they don't need to support multiple platforms) but not really usable for the use case of Symfony.

for symfony, i think we need to extend PostgreSQLPlatform

Symfony must not override the platform classes unless we use a dedicated connection just for the messenger. Otherwise, the application won't be able to use their own platforms anymore.

Otherwise, the application won't be able to use their own platforms anymore.
thats what i am worying about too.

using a dedicated connection for messenger would lead to very annoying configuration when sharing the same database and having some entities and such, as schema diff would not understand what is going on. and we would duplicate the connection configuration which is not ideal either.

i gave it some more thought but don't see any good solutions.

we could overwrite the platforms in the doctrine-bridge to re-add the event system and then use the symfony event system in messenger-doctrine. that feels a bit odd however, and i guess there are some architectural reasons why dbal got rid of the events, so re-adding them does not seem great either.

alternatively we would need to figure out with doctrine dbal if there is a way to add an extension point to schema generation without extending the platform classes.

i guess there are some architectural reasons why dbal got rid of the events

Yes, the reasons are detailed here: doctrine/dbal#5784

If I update doctrine/orm to version 3 that causes an update of dbal to v. 4 as well.
When I did that, LoginFormAutherticator did not work anymore. Is that related with this issue?

If I update doctrine/orm to version 3 that causes an update of dbal to v. 4 as well.

No. It enables the update, but you can choose to remain on DBAL 3.

When I did that, LoginFormAutherticator did not work anymore. Is that related with this issue?

No. Please use our discussions board for further support questions. Thank you.

On an additional note: after migrating to orm:3.1 and dbal:4.0.1 I noticed that the command
bin/console doctrine:schema:validate
reported that a new migration DROP TABLE doctrine_migration_versions was added.

Probably this is something that should be checked when implementing the support to dbal v4.x

reported that a new migration DROP TABLE doctrine_migration_versions was added.

thanks for reporting this @thePanz

that sounds like the https://github.com/doctrine/DoctrineMigrationsBundle has the same problem with dbal as we see for messenger. i think this report belongs into the migrations bundle repository, afaik the symfony repository does not interact with migrations. can you report it there (and link the discussion here?) - i am still hoping for more input from the dbal maintainers. if i understand correctly, the migrations example shows quite nicely that we need some more tooling as that bundle can not just extend all database drivers to add its logic.

@dbu that issue is being tracked at least here.

I'm not sure this is really the exact same issue as with messenger. If it is, the migrations issue seems fixable by leveraging asset filtering.

I believe one solution would be to introduce new extension points in SchemaTool instead, one each for createSchema, updateSchema, dropSchema right at the end. This could provide enough context for other tools to add their own schema management at this point.

extension points on the SchemaTool sounds like a good idea to me.

the important part is to have events or register something on the SchemaTool and not require to use class inheritance as extension point. do you prefer events or registering handlers on the SchemaTool? if its not very urgent, i can try to propose something later this week once i know which direction i should take.

@beberlei @dbu it seems @wmouwen is already proposing something like this: doctrine/orm#11374 albeit just for one event. Would that fit your needs / fit in your proposal?

thanks for the note. we used to interact with the Doctrine\DBAL\Event\SchemaCreateTableEventArgs event. should we add such an event in the SchemaTool of the orm?

if i understand correctly, looking at the full schema would be cumbersome, but i can try to see if that would be doable too.

The new event proposed in the ORM won't help here: it does not allow changing the generated SQL, which is what is done for messenger.

@greg0ire would it make sense if i add something to https://github.com/doctrine/orm/blob/3.1.x/src/Tools/Event/GenerateSchemaTableEventArgs.php to allow adding additional sql there? or should we do another event to allow to add sql when the schema for a table is translated into sql statements?

can that be done within orm or do i need to change something in dbal to make that possible?

I'm not sure… why is custom SQL needed in the first place? If it's because DBAL doesn't have a proper object representation of what's created by that custom SQL, then there are going to be issues when diffing, right? How is that issue fixed with DBAL 3?

the operation is to add a "trigger":

i don't know how exactly it worked with dbal3 - i think that the extra sql was also added when doing the diff and made it so there was no diff detected when there were no other changes.

The diff that is performed is a diff between schemas, right? Does DBAL really allow introspecting triggers, and exposes object representing them in its API? Or do such things actually never come up when introspecting a database into a Schema object?

The new event proposed in the ORM won't help here: it does not allow changing the generated SQL, which is what is done for messenger.

The listener is supposed to execute their own sql, an api that adds more SQL strings is not planned anymore

so would we have to listen for migration events and run the extra sql each time a migration happens? (or query the database to decide if we have to run the sql...)

i really liked how this was able to hook into migrations, both for transparency for the user and to avoid database change decisions at runtime.

we are discussing an event in orm here: doctrine/orm#11409

Thank you for this suggestion.
There has not been a lot of activity here for a while. Would you still like to see this feature?
Every feature is developed by the community.
Perhaps someone would like to try?
You can read how to contribute to get started.

the issue is still relevant. the solution unfortunately did not progress yet.

Thank you for this suggestion.
There has not been a lot of activity here for a while. Would you still like to see this feature?
Every feature is developed by the community.
Perhaps someone would like to try?
You can read how to contribute to get started.

Dbal 4 is more than 1yr old (Feb 3, 2024) and this is still relevant

Isn't this solved already? What's missing?

I don't know, but the official recipe still forces dbal 3 https://github.com/symfony/orm-pack/blob/main/composer.json
with a "Forbid DBAL v4 for now" commit message.

IIRC, the missing part is the automatic schema management for the Messenger doctrine transport, as MessengerTransportDoctrineSchemaListener relies on the onSchemaCreateTable event that allowed it to modify the generated SQL for the table creation, which does not work in DBAL 4.

All other parts of Symfony are compatible with DBAL 4

So… should we reopen doctrine/orm#11409 ?

So… should we reopen doctrine/orm#11409 ?

yes, please! @greg0ire, @stof, I believe so!

Reopened. The next steps would be to work on doctrine/orm#11409 (comment), or at least, clarify what needs to be done.

Here is a summary of what the remaining problem is.
TLDR: When using ORM3+DBAL4, the following postgresql code is not generated/executed:

CREATE OR REPLACE FUNCTION notify_custom_table() RETURNS TRIGGER AS $$
    BEGIN
        PERFORM pg_notify('custom_table', NEW.queue_name::text);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;;
DROP TRIGGER IF EXISTS notify_trigger ON custom_table;;
CREATE TRIGGER notify_trigger AFTER INSERT OR UPDATE ON custom_table FOR EACH ROW EXECUTE PROCEDURE notify_custom_table();

The doctrine/orm#11409 that beberlei created introduces a new event ToolEvents::postSchemaChanged. This event is triggered by two method calls: SchemaTool::createSchema and SchemaTool::updateSchema at the end of the methods. If we rely on this new event instead of the previous onSchemaCreateTable event, the problem will be that we won't be able to see the code to run in one piece. Neither the doctrine migration nor the schema:create / schema:update --dump-sql commands will trigger the event.

MessengerTransportDoctrineSchemaListener works fine with the PR after a little modification if you use scheme:create / update:

--- a/src/Symfony/Bridge/Doctrine/SchemaListener/MessengerTransportDoctrineSchemaListener.php	(revision 59d8c637fca3f4b42fa435d7125923246a787219)
+++ b/src/Symfony/Bridge/Doctrine/SchemaListener/MessengerTransportDoctrineSchemaListener.php	(date 1745501951899)
@@ -13,6 +13,7 @@
 
 use Doctrine\DBAL\Event\SchemaCreateTableEventArgs;
 use Doctrine\ORM\Tools\Event\GenerateSchemaEventArgs;
+use Doctrine\ORM\Tools\Event\SchemaChangedEventArgs;
 use Symfony\Component\Messenger\Bridge\Doctrine\Transport\DoctrineTransport;
 use Symfony\Component\Messenger\Transport\TransportInterface;
 
@@ -44,6 +45,33 @@
         }
     }
 
+    /**
+     * uses https://github.com/doctrine/orm/pull/11409
+     *
+     * @param SchemaChangedEventArgs $event
+     * @return void
+     * @throws \Doctrine\DBAL\Exception
+     */
+    public function postSchemaChanged(SchemaChangedEventArgs $event): void
+    {
+        $connection = $event->getEntityManager()->getConnection();
+        foreach ($event->getSchema()->getTables() as $table) {
+            foreach ($this->transports as $transport) {
+                if (!$transport instanceof DoctrineTransport) {
+                    continue;
+                }
+
+                if (!$extraSql = $transport->getExtraSetupSqlForTable($table)) {
+                    continue;
+                }
+
+                foreach ($extraSql as $sql) {
+                    $connection->executeStatement($sql);
+                }
+            }
+        }
+    }
+
     public function onSchemaCreateTable(SchemaCreateTableEventArgs $event): void
     {
         $table = $event->getTable();

(The doctrine/doctrine-bundle/config/messenger.xml should also be updated.)

However, the doctrine migrations remain broken.

doctrine/orm#11409 is not the solution to that, as it does not hook into the schema diffing logic which is common to various places (the doctrine:schema:* commands but also the diff done in doctrine/migrations), but only in the logic performing execution of changes for the doctrine:schema:*. And it currently does not allow listeners to provide extra queries to run but expects them to execute them directly, which also breaks when using the --dry-runor--dump-sql` options of those schema commands (as the way the SQL queries should be used depend on those flags which are not exposed to the listeners, or maybe the listeners are not called at all).

We need a solution that works for usages with doctrine/migrations as well.

@stof should I close doctrine/orm#11409 again or are you saying something extra will still be needed on top of it?

I think you can close this PR again, as that event is not solving the need (and I don't think this event would be part of a working solution covering the need).

however, it might make sense to ensure there is an open issue on the Doctrine side (not sure in which project) to track this need.

I guess that open issue is doctrine/orm#10208