Custom Index definition with postgresql #5723

New Issue

admin · 2026-01-22T15:15:38+01:00

admin commented

2026-01-22 15:15:38 +01:00

Originally created by @Vincz on GitHub (Oct 2, 2017).

Originally assigned to: @Ocramius on GitHub.

Hi! I was wondering how it would be possible to create GIN / GIST indexes with doctrine and the postgresql adapter.
Should we create our own type, like this:
https://github.com/jsor/doctrine-postgis/blob/master/src/Schema/SpatialIndexSqlGenerator.php

Or is there a built-in way in doctrine. Can't find any documentation on this.

Just trying to use the indexes from here : https://www.postgresql.org/docs/9.1/static/textsearch-indexes.html

Thx !

Originally created by @Vincz on GitHub (Oct 2, 2017). Originally assigned to: @Ocramius on GitHub. Hi! I was wondering how it would be possible to create GIN / GIST indexes with doctrine and the postgresql adapter. Should we create our own type, like this: https://github.com/jsor/doctrine-postgis/blob/master/src/Schema/SpatialIndexSqlGenerator.php Or is there a built-in way in doctrine. Can't find any documentation on this. Just trying to use the indexes from here : https://www.postgresql.org/docs/9.1/static/textsearch-indexes.html Thx !

admin added the Improvement Question labels 2026-01-22 15:15:38 +01:00

admin closed this issue

2026-01-22 15:15:38 +01:00

admin commented

2026-01-22 15:15:39 +01:00

@Ocramius commented on GitHub (Oct 3, 2017):

Or is there a built-in way in doctrine. Can't find any documentation on this.

No, the ORM has no way to map these index types at the moment.

In my opinion, this kind of information should only be mapped in SQL migrations, as it starts becoming extremely DB-vendor-specific.

@Ocramius commented on GitHub (Oct 3, 2017): > Or is there a built-in way in doctrine. Can't find any documentation on this. No, the ORM has no way to map these index types at the moment. In my opinion, this kind of information should only be mapped in SQL migrations, as it starts becoming extremely DB-vendor-specific.

admin commented

2026-01-22 15:15:40 +01:00

@lcobucci commented on GitHub (Nov 26, 2017):

Closing as per @Ocramius' comment.

@lcobucci commented on GitHub (Nov 26, 2017): Closing as per @Ocramius' comment.

admin commented

2026-01-22 15:15:40 +01:00

@Grafikart commented on GitHub (Aug 16, 2020):

Sorry to reopen such an old issue but I get the same problem. I add my index in migrations as you suggested but when I try to generate other migrations doctrine try to delete the index.

Is there a way to tell doctrine to ignore some custom indexes (inserted without annotation but with migrations) ?

@Grafikart commented on GitHub (Aug 16, 2020): Sorry to reopen such an old issue but I get the same problem. I add my index in migrations as you suggested but when I try to generate other migrations doctrine try to delete the index. Is there a way to tell doctrine to ignore some custom indexes (inserted without annotation but with migrations) ?

admin commented

2026-01-22 15:15:41 +01:00

@Grafikart commented on GitHub (Aug 16, 2020):

If someone find this issue I found a post about this problem. It may be a good solution : https://www.liip.ch/en/blog/doctrine-and-generated-columns

@Grafikart commented on GitHub (Aug 16, 2020): If someone find this issue I found a post about this problem. It may be a good solution : https://www.liip.ch/en/blog/doctrine-and-generated-columns

admin commented

2026-01-22 15:15:41 +01:00

@alexsegura commented on GitHub (Sep 29, 2020):

You can do it like this, using index flags & a custom platform class.

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="App\Person" table="person">
    <indexes>
      <index columns="first_name" flags="fulltext"/>
      <index columns="last_name" flags="fulltext"/>
    </indexes>
    <field name="firstName" column="first_name" type="string" nullable="true" />
    <field name="lastName" column="last_name" type="string" nullable="true" />
  </entity>
</doctrine-mapping>

<?php

namespace App\Doctrine\DBAL\Platforms;

use Doctrine\DBAL\Platforms\PostgreSQL94Platform as BasePostgreSQL94Platform;
use Doctrine\DBAL\Schema\Index;
use Doctrine\DBAL\Schema\Table;

class PostgreSQL94Platform extends BasePostgreSQL94Platform
{
    public function getIndexFieldDeclarationListSQL($columnsOrIndex): string
    {
        if ($columnsOrIndex instanceof Index && $columnsOrIndex->hasFlag('fulltext')) {

            return implode(', ', array_map(
                fn($column) => sprintf('%s gist_trgm_ops', $column),
                $columnsOrIndex->getQuotedColumns($this)
            ));
        }

        return parent::getIndexFieldDeclarationListSQL($columnsOrIndex);
    }


    public function getCreateIndexSQL(Index $index, $table)
    {
        if (!$index->hasFlag('fulltext')) {
            return parent::getCreateIndexSQL($index, $table);
        }

        if ($table instanceof Table) {
            $table = $table->getQuotedName($this);
        }

        $table = sprintf('%s USING GIST', $table);

        return parent::getCreateIndexSQL($index, $table);
    }
}

If you are using Symfony, you can override the platform class using the platform_service configuration key.

@alexsegura commented on GitHub (Sep 29, 2020): You can do it like this, using [index flags](https://www.doctrine-project.org/projects/doctrine-orm/en/2.5/changelog/migration_2_5.html#mapping-allow-configuring-index-flags) & a custom platform class. ```xml <?xml version="1.0" encoding="utf-8"?> <doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd"> <entity name="App\Person" table="person"> <indexes> <index columns="first_name" flags="fulltext"/> <index columns="last_name" flags="fulltext"/> </indexes> <field name="firstName" column="first_name" type="string" nullable="true" /> <field name="lastName" column="last_name" type="string" nullable="true" /> </entity> </doctrine-mapping> ``` ```php <?php namespace App\Doctrine\DBAL\Platforms; use Doctrine\DBAL\Platforms\PostgreSQL94Platform as BasePostgreSQL94Platform; use Doctrine\DBAL\Schema\Index; use Doctrine\DBAL\Schema\Table; class PostgreSQL94Platform extends BasePostgreSQL94Platform { public function getIndexFieldDeclarationListSQL($columnsOrIndex): string { if ($columnsOrIndex instanceof Index && $columnsOrIndex->hasFlag('fulltext')) { return implode(', ', array_map( fn($column) => sprintf('%s gist_trgm_ops', $column), $columnsOrIndex->getQuotedColumns($this) )); } return parent::getIndexFieldDeclarationListSQL($columnsOrIndex); } public function getCreateIndexSQL(Index $index, $table) { if (!$index->hasFlag('fulltext')) { return parent::getCreateIndexSQL($index, $table); } if ($table instanceof Table) { $table = $table->getQuotedName($this); } $table = sprintf('%s USING GIST', $table); return parent::getCreateIndexSQL($index, $table); } } ``` If you are using Symfony, you can override the platform class using the [`platform_service`](https://symfony.com/doc/current/reference/configuration/doctrine.html) configuration key.

admin commented

2026-01-22 15:15:41 +01:00

@SherinBloemendaal commented on GitHub (Sep 12, 2022):

The method getIndexFieldDeclarationListSQL is deprecated since https://github.com/doctrine/dbal/pull/5527. How should we handle this case in 2022?

@SherinBloemendaal commented on GitHub (Sep 12, 2022): The method `getIndexFieldDeclarationListSQL` is deprecated since https://github.com/doctrine/dbal/pull/5527. How should we handle this case in 2022?

admin commented

2026-01-22 15:15:42 +01:00

@janklan commented on GitHub (Nov 20, 2022):

getIndexFieldDeclarationListSQL

I'm not sure why they used @deprecated and not @internal, but the code in AbstractPlatform::getIndexFieldDeclarationListSQL() points at https://github.com/doctrine/dbal/pull/5527, which says the methods will be made protected, not removed, so I think @alexsegura's code is still valid.

@janklan commented on GitHub (Nov 20, 2022): > ``` > getIndexFieldDeclarationListSQL > ``` I'm not sure why they used `@deprecated` and not `@internal`, but the code in `AbstractPlatform::getIndexFieldDeclarationListSQL()` points at https://github.com/doctrine/dbal/pull/5527, which says the methods will be made protected, not removed, so I think @alexsegura's code is still valid.

admin commented

2026-01-22 15:15:42 +01:00

@DaanBiesterbos commented on GitHub (Mar 21, 2024):

Regardless of which annotation is used, of what we assume. Fact is that it is deprecated. The "platform_service" configuration is now also deprecated in the doctrine-bundle for Symfony.

I need 1 thing. To add a GIS index to a migration file, and stop doctrine from interfering with it. And it looks like all I have are deprecated options and assumptions. I cannot deliver this to a client. I went over a number of issues. And I've only seen proposals being rejected. And I have read some excellent points which are all true about how the ORM should work. All of which are true.
However, in some cases, database specific features are needed. And I feel like doctrine is cutting off as many non standard use cases as possible. For the sake of simplicity, or performance maybe. But I feel like this is a mistake.

Either provide a solution (like a custom index) to allow us to use database specific features. (Without having doctrine try to remove all our indexes every single migration.......) Of provide a way to extend doctrine. Let us extend the Platform class. Don't deprecate it unless there is an alternative.

If I am wrong, I would love to hear it. As it would solve my problem.
But I just want a GIS index, and use tsvector functions in postgres (which I can add via the custom functions.
Why is Doctrine making it so difficult? Sure it should not be in the ORM, 100% true, but provide a way way to customize the index definition.

How is it possible that we can hook into the SQL parser, add custom functions, add custom column definitions. But customizing a simple index is a bridge too far.
As far as I am concerned adding an index should just be be possible. There is clearly a use case for it. Or is doctrine also planning to remove custom column definitions and support for custom functions? I certainly hope not...

I just need an index. And I need Doctrine to stop trying to remove the index(es) every time I create a migration. This is bad, annoying and could lead to serious problems when overlooked.
I hope you'll are willing to go over the I don't know how many related tickets and come to the same conclusion. Awesome work over the years guys, really. But please, fix this. We need this.

@DaanBiesterbos commented on GitHub (Mar 21, 2024): Regardless of which annotation is used, of what we assume. Fact is that it is deprecated. The "platform_service" configuration is now also deprecated in the doctrine-bundle for Symfony. I need 1 thing. To add a GIS index to a migration file, and stop doctrine from interfering with it. And it looks like all I have are deprecated options and assumptions. I cannot deliver this to a client. I went over a number of issues. And I've only seen proposals being rejected. And I have read some excellent points which are all true about how the ORM should work. All of which are true. However, in some cases, database specific features are **needed**. And I feel like doctrine is cutting off as many non standard use cases as possible. For the sake of simplicity, or performance maybe. But I feel like this is a mistake. Either provide a solution (like a custom index) to allow us to use database specific features. (Without having doctrine try to remove all our indexes every single migration.......) Of provide a way to extend doctrine. Let us extend the Platform class. Don't deprecate it unless there is an alternative. If I am wrong, I would love to hear it. As it would solve my problem. But I just want a GIS index, and use tsvector functions in postgres (which I can add via the custom functions. Why is Doctrine making it so difficult? Sure it should not be in the ORM, 100% true, but provide a way way to customize the index definition. How is it possible that we can hook into the SQL parser, add custom functions, add custom column definitions. But customizing a simple index is a bridge too far. As far as I am concerned adding an index should just be be possible. There is clearly a use case for it. Or is doctrine also planning to remove custom column definitions and support for custom functions? I certainly hope not... I just need an index. And I need Doctrine to stop trying to remove the index(es) every time I create a migration. This is bad, annoying and could lead to serious problems when overlooked. I hope you'll are willing to go over the I don't know how many related tickets and come to the same conclusion. Awesome work over the years guys, really. But please, fix this. We need this.

admin commented

2026-01-22 15:15:43 +01:00

@DaanBiesterbos commented on GitHub (Mar 22, 2024):

This solution is also deprecated:
https://www.liip.ch/en/blog/doctrine-and-generated-columns

What is doctrine providing as an alternative?

@DaanBiesterbos commented on GitHub (Mar 22, 2024): This solution is also deprecated: https://www.liip.ch/en/blog/doctrine-and-generated-columns What is doctrine providing as an alternative?

admin commented

2026-01-22 15:15:43 +01:00

@greg0ire commented on GitHub (Mar 22, 2024):

I went over a number of issues

It feels to me like you did not find this one: https://github.com/doctrine/migrations/issues/831

@greg0ire commented on GitHub (Mar 22, 2024): > I went over a number of issues It feels to me like you did not find this one: https://github.com/doctrine/migrations/issues/831

admin commented

2026-01-22 15:15:44 +01:00

@DaanBiesterbos commented on GitHub (Mar 25, 2024):

https://www.liip.ch/en/blog/doctrine-and-generated-columns

I found a similar example. I liked it... But these listeners are all deprecated in Symfony 6....

@DaanBiesterbos commented on GitHub (Mar 25, 2024): > https://www.liip.ch/en/blog/doctrine-and-generated-columns I found a similar example. I liked it... But these listeners are all deprecated in Symfony 6....

admin commented

2026-01-22 15:15:44 +01:00

@DaanBiesterbos commented on GitHub (Mar 25, 2024):

doctrine/migrations#831

I did, but the issue is open. And I noticed a related PR that was rejected.
There is a comment that links to this solution:
https://medium.com/yousign-engineering-product/ignore-custom-indexes-on-doctrine-dbal-b5131dd22071
These methods are also deprecated. So is the platform_service setting in the doctrine-bundle.

@DaanBiesterbos commented on GitHub (Mar 25, 2024): > doctrine/migrations#831 I did, but the issue is open. And I noticed a related PR that was rejected. There is a comment that links to this solution: https://medium.com/yousign-engineering-product/ignore-custom-indexes-on-doctrine-dbal-b5131dd22071 These methods are also deprecated. So is the platform_service setting in the doctrine-bundle.

admin commented

2026-01-22 15:15:44 +01:00

@greg0ire commented on GitHub (Mar 25, 2024):

There is a comment that links to this solution:

Yes, that is what I was referring to.

These methods are also deprecated.

What methods?

So is the platform_service setting in the doctrine-bundle.

Simply add the “schema_manager_factory” config key in your connection configuration with your own factory service, or if you already override the doctrine platform service with your own, we just need to override a method and don’t need a custom SchemaManagerFactory. We can simply use the doctrine DefaultSchemaManagerFactory instead of creating our own.

Looks like you are not forced to override the platform service. overriding the schema manager factory might be enough.

@greg0ire commented on GitHub (Mar 25, 2024): > There is a comment that links to this solution: Yes, that is what I was referring to. > These methods are also deprecated. What methods? > So is the platform_service setting in the doctrine-bundle. > Simply add the “schema_manager_factory” config key in your connection configuration with your own factory service, **or** if you already override the doctrine platform service with your own, we just need to override a method and don’t need a custom SchemaManagerFactory. We can simply use the doctrine DefaultSchemaManagerFactory instead of creating our own. Looks like you are not forced to override the platform service. overriding the schema manager factory might be enough.

admin commented

2026-01-22 15:15:45 +01:00

@DaanBiesterbos commented on GitHub (Mar 25, 2024):

There is a comment that links to this solution:

Yes, that is what I was referring to.

These methods are also deprecated.

What methods?

So is the platform_service setting in the doctrine-bundle.

Simply add the “schema_manager_factory” config key in your connection configuration with your own factory service, or if you already override the doctrine platform service with your own, we just need to override a method and don’t need a custom SchemaManagerFactory. We can simply use the doctrine DefaultSchemaManagerFactory instead of creating our own.

Looks like you are not forced to override the platform service. overriding the schema manager factory might be enough.

Thank you for your response. I tried it. Unfortunately, when I do this the customizations from doctrine.yaml are not loaded (types etc). And the configured doctrine platform is not a service. So I cannot inject it.

When I override the schema manager factory, I get this exception:
Unknown database type bit requested, Doctrine\DBAL\Platforms\PostgreSQLPlatform may not support it.
When I don't extend the platform there is no service for the platform. So I guess it makes sense. Whether I instantiate the platform myself or inject a new instance, it seems to be separate from Symfony without the platform_service setting.

Maybe I missed something somewhere, but since the original platform is not available as a service I decided not to waste too much time on it.

When I use the platform service is does seem to work (although I did not test everything).
Sadly, the platform_service setting is also deprecated.

I tried the subscriber as well.
I can automatically generate the index this way. Which would be nice, were it not that all these events are deprecated. And don't work exactly as I would expect. Well, for the column, I do understand that the index definition event might depend on the index attributes, which are not available for custom indexes.
So far, I cannot keep doctrine from 1. trying to delete the index and 2. alter the tsvector column definition every time I generate a new migration.
I verified that the preventDefault method is invoked for the altered column, but it would seem the alter table statements are rendered regardless.

#[AsDoctrineListener(event: Events::onSchemaAlterTableAddColumn, priority: 1000)]
#[AsDoctrineListener(event: Events::onSchemaAlterTableChangeColumn, priority: 1000)]
// #[AsDoctrineListener(event: Events::onSchemaIndexDefinition, priority: 1000)]
class DoctrineSchemaSubscriber
{
    public function onSchemaAlterTableChangeColumn(SchemaAlterTableChangeColumnEventArgs $eventArgs)
    {
        // Postgres does not allow "GENERATED AS" statement in alter column statements.
        // The column needs to be dropped and recreated. 
        // We'll need to add a drop column statement and recreate the column in the migration file.
        if ($eventArgs->getColumnDiff()->getOldColumn()->getType()->getName() === 'tsvector') {
            $eventArgs->preventDefault();
        }

        return $eventArgs;
    }

    public function onSchemaAlterTableAddColumn(SchemaAlterTableAddColumnEventArgs $eventArgs)
    {
        // Create search index
        if ($eventArgs->getColumn()->getType()->getName() === 'tsvector') {
            $table = $eventArgs->getTableDiff()->getOldTable()->getName();
            $columnName = $eventArgs->getColumn()->getName();
            $indexHash = md5("textsearch_{$table}_$columnName");
            $eventArgs->addSql("CREATE INDEX IF NOT EXISTS tsidx_$indexHash ON product USING gin($columnName)");
        }
    }

    // public function onSchemaIndexDefinition(SchemaIndexDefinitionEventArgs $eventArgs)
    // {
    //    // Keep doctrine from dropping the text search index (attempt to, I am not convinced that this will work)
    //    if (str_starts_with($eventArgs->getIndex()?->getName(), 'tsidx_')) {
    //        $eventArgs->preventDefault();
    //    }
    // }
}

This would be perfect.......... If it worked....

The first migration looks like this:

$this->addSql('ALTER TABLE product ADD search_index tsvector GENERATED ALWAYS AS(setweight(to_tsvector(\'dutch\', COALESCE(ean, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(title, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(product_code, \'\')), \'B\') || setweight(to_tsvector(\'dutch\', coalesce(advertiser_name, \'\')), \'C\')) STORED NOT NULL');
$this->addSql('CREATE INDEX IF NOT EXISTS tsidx_64a53c77d560db2c7f5577dcfc7d6b7c ON product USING gin(search_index)');

The following four statements are generated every single migration.

$this->addSql('DROP INDEX tsidx_64a53c77d560db2c7f5577dcfc7d6b7c');
$this->addSql('ALTER TABLE product ALTER search_index TYPE tsvector GENERATED ALWAYS AS(setweight(to_tsvector(\'dutch\', COALESCE(ean, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(title, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(product_code, \'\')), \'B\') || setweight(to_tsvector(\'dutch\', coalesce(advertiser_name, \'\')), \'C\')) STORED');
$this->addSql('ALTER TABLE product ALTER search_index DROP DEFAULT');
$this->addSql('ALTER TABLE product ALTER search_index SET NOT NULL');

TsVectorType

class TsVectorType extends Type
{
    public const NAME = 'tsvector';
    public const DEFAULT_LANGUAGE = 'dutch';

    public function getName(): string
    {
        return self::NAME;
    }
    
    public function getSqlDeclaration(array $column, AbstractPlatform $platform): string
    {
        $column['columns'] ??= [];
        $column['language'] ??= self::DEFAULT_LANGUAGE;
        $column['notnull'] ??= true;
        if ($column['notnull'] === false) {
            throw new ORMException("A text search vector must not be nullable.");
        }

        if (!is_array($column['columns']) || count($column['columns']) === 0) {
            throw new ORMException("Missing 'columns' option in #[Column(type: 'tsvector')] attribute");
        }

        $sqlDeclaration = "tsvector GENERATED ALWAYS AS(";

        /**
         * @var string $columnName
         * @var TextSearchPriority $priority
         */
        $lastColumn = array_key_last($column['columns']);
        foreach ($column['columns'] as $columnName => $priority) {
            if ($columnName !== $lastColumn) {
                $sqlDeclaration .= "setweight(to_tsvector('{$column['language']}', COALESCE($columnName, '')), '$priority->value') || ";
                continue;
            }
            $sqlDeclaration .= "setweight(to_tsvector('{$column['language']}', coalesce($columnName, '')), '$priority->value')";
        }

        $sqlDeclaration .= ') STORED';


        return $sqlDeclaration;
    }
    
    // rest of methods
}

Product entity:

class Product
{
    #[Column(
        type: TsVectorType::NAME,
        nullable: false,
        insertable: false,
        updatable: false,
        options: [
            'language' => 'dutch',
            'columns' => [
                'ean' => TextSearchPriority::High,
                'title' => TextSearchPriority::High,
                'product_code' => TextSearchPriority::Normal,
                'advertiser_name' => TextSearchPriority::Low,
            ]
        ],
        generated: 'ALWAYS'
    )]
    private string $searchIndex;
}

@DaanBiesterbos commented on GitHub (Mar 25, 2024): > > There is a comment that links to this solution: > > Yes, that is what I was referring to. > > > These methods are also deprecated. > > What methods? > > > So is the platform_service setting in the doctrine-bundle. > > > Simply add the “schema_manager_factory” config key in your connection configuration with your own factory service, **or** if you already override the doctrine platform service with your own, we just need to override a method and don’t need a custom SchemaManagerFactory. We can simply use the doctrine DefaultSchemaManagerFactory instead of creating our own. > > Looks like you are not forced to override the platform service. overriding the schema manager factory might be enough. Thank you for your response. I tried it. Unfortunately, when I do this the customizations from doctrine.yaml are not loaded (types etc). And the configured doctrine platform is not a service. So I cannot inject it. When I override the schema manager factory, I get this exception: _Unknown database type bit requested, Doctrine\DBAL\Platforms\PostgreSQLPlatform may not support it._ When I don't extend the platform there is no service for the platform. So I guess it makes sense. Whether I instantiate the platform myself or inject a new instance, it seems to be separate from Symfony without the platform_service setting. Maybe I missed something somewhere, but since the original platform is not available as a service I decided not to waste too much time on it. When I use the platform service is does seem to work (although I did not test everything). Sadly, the platform_service setting is also deprecated. I tried the subscriber as well. I can automatically generate the index this way. Which would be nice, were it not that all these events are deprecated. And don't work exactly as I would expect. Well, for the column, I do understand that the index definition event might depend on the index attributes, which are not available for custom indexes. So far, I cannot keep doctrine from 1. trying to delete the index and 2. alter the tsvector column definition every time I generate a new migration. I verified that the preventDefault method is invoked for the altered column, but it would seem the alter table statements are rendered regardless. ``` #[AsDoctrineListener(event: Events::onSchemaAlterTableAddColumn, priority: 1000)] #[AsDoctrineListener(event: Events::onSchemaAlterTableChangeColumn, priority: 1000)] // #[AsDoctrineListener(event: Events::onSchemaIndexDefinition, priority: 1000)] class DoctrineSchemaSubscriber { public function onSchemaAlterTableChangeColumn(SchemaAlterTableChangeColumnEventArgs $eventArgs) { // Postgres does not allow "GENERATED AS" statement in alter column statements. // The column needs to be dropped and recreated. // We'll need to add a drop column statement and recreate the column in the migration file. if ($eventArgs->getColumnDiff()->getOldColumn()->getType()->getName() === 'tsvector') { $eventArgs->preventDefault(); } return $eventArgs; } public function onSchemaAlterTableAddColumn(SchemaAlterTableAddColumnEventArgs $eventArgs) { // Create search index if ($eventArgs->getColumn()->getType()->getName() === 'tsvector') { $table = $eventArgs->getTableDiff()->getOldTable()->getName(); $columnName = $eventArgs->getColumn()->getName(); $indexHash = md5("textsearch_{$table}_$columnName"); $eventArgs->addSql("CREATE INDEX IF NOT EXISTS tsidx_$indexHash ON product USING gin($columnName)"); } } // public function onSchemaIndexDefinition(SchemaIndexDefinitionEventArgs $eventArgs) // { // // Keep doctrine from dropping the text search index (attempt to, I am not convinced that this will work) // if (str_starts_with($eventArgs->getIndex()?->getName(), 'tsidx_')) { // $eventArgs->preventDefault(); // } // } } ``` This would be perfect.......... If it worked.... The first migration looks like this: ``` $this->addSql('ALTER TABLE product ADD search_index tsvector GENERATED ALWAYS AS(setweight(to_tsvector(\'dutch\', COALESCE(ean, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(title, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(product_code, \'\')), \'B\') || setweight(to_tsvector(\'dutch\', coalesce(advertiser_name, \'\')), \'C\')) STORED NOT NULL'); $this->addSql('CREATE INDEX IF NOT EXISTS tsidx_64a53c77d560db2c7f5577dcfc7d6b7c ON product USING gin(search_index)'); ``` The following four statements are generated every single migration. ``` $this->addSql('DROP INDEX tsidx_64a53c77d560db2c7f5577dcfc7d6b7c'); $this->addSql('ALTER TABLE product ALTER search_index TYPE tsvector GENERATED ALWAYS AS(setweight(to_tsvector(\'dutch\', COALESCE(ean, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(title, \'\')), \'A\') || setweight(to_tsvector(\'dutch\', COALESCE(product_code, \'\')), \'B\') || setweight(to_tsvector(\'dutch\', coalesce(advertiser_name, \'\')), \'C\')) STORED'); $this->addSql('ALTER TABLE product ALTER search_index DROP DEFAULT'); $this->addSql('ALTER TABLE product ALTER search_index SET NOT NULL'); ``` TsVectorType ``` class TsVectorType extends Type { public const NAME = 'tsvector'; public const DEFAULT_LANGUAGE = 'dutch'; public function getName(): string { return self::NAME; } public function getSqlDeclaration(array $column, AbstractPlatform $platform): string { $column['columns'] ??= []; $column['language'] ??= self::DEFAULT_LANGUAGE; $column['notnull'] ??= true; if ($column['notnull'] === false) { throw new ORMException("A text search vector must not be nullable."); } if (!is_array($column['columns']) || count($column['columns']) === 0) { throw new ORMException("Missing 'columns' option in #[Column(type: 'tsvector')] attribute"); } $sqlDeclaration = "tsvector GENERATED ALWAYS AS("; /** * @var string $columnName * @var TextSearchPriority $priority */ $lastColumn = array_key_last($column['columns']); foreach ($column['columns'] as $columnName => $priority) { if ($columnName !== $lastColumn) { $sqlDeclaration .= "setweight(to_tsvector('{$column['language']}', COALESCE($columnName, '')), '$priority->value') || "; continue; } $sqlDeclaration .= "setweight(to_tsvector('{$column['language']}', coalesce($columnName, '')), '$priority->value')"; } $sqlDeclaration .= ') STORED'; return $sqlDeclaration; } // rest of methods } ``` Product entity: ``` class Product { #[Column( type: TsVectorType::NAME, nullable: false, insertable: false, updatable: false, options: [ 'language' => 'dutch', 'columns' => [ 'ean' => TextSearchPriority::High, 'title' => TextSearchPriority::High, 'product_code' => TextSearchPriority::Normal, 'advertiser_name' => TextSearchPriority::Low, ] ], generated: 'ALWAYS' )] private string $searchIndex; } ```

admin commented

2026-01-22 15:15:45 +01:00

@DaanBiesterbos commented on GitHub (Mar 26, 2024):

Edit:

I was able to fix the subscriber. That works fine. Although the events are still deprecated. And it does not yet avoid the deletion of the index.

#[AsDoctrineListener(event: Events::onSchemaAlterTableAddColumn, priority: 1000)]
#[AsDoctrineListener(event: Events::onSchemaAlterTableChangeColumn, priority: 1000)]
class DoctrineSchemaSubscriber
{
    public function onSchemaAlterTableChangeColumn(SchemaAlterTableChangeColumnEventArgs $eventArgs)
    {
        // Postgres does not allow "GENERATED AS" statement in alter column statements.
        // The column needs to be dropped and recreated.
        // We'll need to add a drop column statement and recreate the column in the migration file.
        if ($eventArgs->getColumnDiff()->getNewColumn()->getType() instanceof TsVectorType) {
            $eventArgs->preventDefault();
        }

        return $eventArgs;
    }

    public function onSchemaAlterTableAddColumn(SchemaAlterTableAddColumnEventArgs $eventArgs)
    {
        // Create search index
        if ($eventArgs->getColumn()->getType()->getName() === 'tsvector') {
            $table = $eventArgs->getTableDiff()->getOldTable()->getName();
            $columnName = $eventArgs->getColumn()->getName();
            $indexHash = md5("textsearch_{$table}_$columnName");
            $eventArgs->addSql("CREATE INDEX IF NOT EXISTS tsidx_$indexHash ON product USING gin($columnName)");
        }
    }
}

I assume that the only solution that solves all my problems will be the platform_service...

@DaanBiesterbos commented on GitHub (Mar 26, 2024): Edit: I was able to fix the subscriber. That works fine. Although the events are still deprecated. And it does not yet avoid the deletion of the index. ``` #[AsDoctrineListener(event: Events::onSchemaAlterTableAddColumn, priority: 1000)] #[AsDoctrineListener(event: Events::onSchemaAlterTableChangeColumn, priority: 1000)] class DoctrineSchemaSubscriber { public function onSchemaAlterTableChangeColumn(SchemaAlterTableChangeColumnEventArgs $eventArgs) { // Postgres does not allow "GENERATED AS" statement in alter column statements. // The column needs to be dropped and recreated. // We'll need to add a drop column statement and recreate the column in the migration file. if ($eventArgs->getColumnDiff()->getNewColumn()->getType() instanceof TsVectorType) { $eventArgs->preventDefault(); } return $eventArgs; } public function onSchemaAlterTableAddColumn(SchemaAlterTableAddColumnEventArgs $eventArgs) { // Create search index if ($eventArgs->getColumn()->getType()->getName() === 'tsvector') { $table = $eventArgs->getTableDiff()->getOldTable()->getName(); $columnName = $eventArgs->getColumn()->getName(); $indexHash = md5("textsearch_{$table}_$columnName"); $eventArgs->addSql("CREATE INDEX IF NOT EXISTS tsidx_$indexHash ON product USING gin($columnName)"); } } } I assume that the only solution that solves all my problems will be the platform_service... ```

admin commented

2026-01-22 15:15:46 +01:00

@DaanBiesterbos commented on GitHub (Mar 26, 2024):

This was the best solution I could come up with. I still need the platform_service. But I don't need the subscriber anymore. The solution should be okayish. Although that's not really what I like to be aiming for.

I extended the PostgreSQLPlatform class:

use App\Shared\Infrastructure\Framework\Doctrine\DBAL\Schema\PostgreSQLSchemaManager;
use App\Shared\Infrastructure\Framework\Doctrine\DBAL\Types\TsVectorType;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform as BasePostgreSQLPlatform;
use Doctrine\DBAL\Schema\Column;
use Doctrine\DBAL\Schema\ColumnDiff;
use Doctrine\DBAL\Schema\PostgreSQLSchemaManager as BasePostgreSQLSchemaManager;
use Doctrine\DBAL\Schema\SchemaManagerFactory;
use Doctrine\DBAL\Schema\TableDiff;

class PostgreSQLPlatform extends BasePostgreSQLPlatform implements SchemaManagerFactory
{
    public const TEXT_SEARCH_INDEX_PREFIX = 'tsidx';

    protected function onSchemaAlterTableAddColumn(Column $column, TableDiff $diff, &$columnSql)
    {
        if ($column->getType() instanceof TsVectorType) {
            $table = $diff->getOldTable()->getName();
            $columnName = $column->getName();
            $indexHash = md5("textsearch_{$table}_$columnName");
            $indexPrefix = self::TEXT_SEARCH_INDEX_PREFIX;
            $columnSql[] = "CREATE INDEX IF NOT EXISTS {$indexPrefix}_$indexHash ON product USING gin($columnName)";

            return false;
        }

        return parent::onSchemaAlterTableAddColumn($column, $diff, $columnSql);
    }

    protected function onSchemaAlterTableChangeColumn(ColumnDiff $columnDiff, TableDiff $diff, &$columnSql): bool
    {
        // Check both to apply exclusion to both up() and down() methods
        $oldColumnType = $columnDiff->getOldColumn()->getType();
        $newColumnType = $columnDiff->getNewColumn()->getType();
        if ($oldColumnType instanceof TsVectorType || $newColumnType instanceof TsVectorType) {
            $columnSql = [];
            return true;
        }

        return parent::onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql);
    }

    public function createSchemaManager(Connection $connection): BasePostgreSQLSchemaManager
    {
        return new PostgreSQLSchemaManager($connection, $this);
    }
}

Extend PostgreSQLSchemaManager:

use App\Shared\Infrastructure\Framework\Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Schema\PostgreSQLSchemaManager as BasePostgreSQLSchemaManager;

/**
 * Important:
 * This class is needed to keep doctrine from generating code that removes our search index, every time we generate a
 * migration. Doctrine does not support custom indexes. We need to extend doctrine to implement this functionality.
 * We can remove this class, but we would need to very careful not to delete our search index every time we generate
 * a migration.
 */
class PostgreSQLSchemaManager extends BasePostgreSQLSchemaManager
{
    /**
     * This method is needed to keep doctrine from removing the (custom) search index when we generate a migration.
     */
    protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
    {
        $indexes = parent::_getPortableTableIndexesList($tableIndexes, $tableName);

        foreach ($indexes as $indexName => $index) {
            if (str_starts_with($indexName, PostgreSQLPlatform::TEXT_SEARCH_INDEX_PREFIX)) {
                unset($indexes[$indexName]);
            }
        }

        return $indexes;
    }
}

Configuration in packages/doctrine.yaml:

doctrine:
    dbal:
        platform_service: App\Shared\Infrastructure\Framework\Doctrine\DBAL\Platforms\PostgreSQLPlatform
        schema_manager_factory: App\Shared\Infrastructure\Framework\Doctrine\DBAL\Platforms\PostgreSQLPlatform

@DaanBiesterbos commented on GitHub (Mar 26, 2024): This was the best solution I could come up with. I still need the platform_service. But I don't need the subscriber anymore. The solution should be okayish. Although that's not really what I like to be aiming for. I extended the PostgreSQLPlatform class: ``` use App\Shared\Infrastructure\Framework\Doctrine\DBAL\Schema\PostgreSQLSchemaManager; use App\Shared\Infrastructure\Framework\Doctrine\DBAL\Types\TsVectorType; use Doctrine\DBAL\Connection; use Doctrine\DBAL\Platforms\PostgreSQLPlatform as BasePostgreSQLPlatform; use Doctrine\DBAL\Schema\Column; use Doctrine\DBAL\Schema\ColumnDiff; use Doctrine\DBAL\Schema\PostgreSQLSchemaManager as BasePostgreSQLSchemaManager; use Doctrine\DBAL\Schema\SchemaManagerFactory; use Doctrine\DBAL\Schema\TableDiff; class PostgreSQLPlatform extends BasePostgreSQLPlatform implements SchemaManagerFactory { public const TEXT_SEARCH_INDEX_PREFIX = 'tsidx'; protected function onSchemaAlterTableAddColumn(Column $column, TableDiff $diff, &$columnSql) { if ($column->getType() instanceof TsVectorType) { $table = $diff->getOldTable()->getName(); $columnName = $column->getName(); $indexHash = md5("textsearch_{$table}_$columnName"); $indexPrefix = self::TEXT_SEARCH_INDEX_PREFIX; $columnSql[] = "CREATE INDEX IF NOT EXISTS {$indexPrefix}_$indexHash ON product USING gin($columnName)"; return false; } return parent::onSchemaAlterTableAddColumn($column, $diff, $columnSql); } protected function onSchemaAlterTableChangeColumn(ColumnDiff $columnDiff, TableDiff $diff, &$columnSql): bool { // Check both to apply exclusion to both up() and down() methods $oldColumnType = $columnDiff->getOldColumn()->getType(); $newColumnType = $columnDiff->getNewColumn()->getType(); if ($oldColumnType instanceof TsVectorType || $newColumnType instanceof TsVectorType) { $columnSql = []; return true; } return parent::onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql); } public function createSchemaManager(Connection $connection): BasePostgreSQLSchemaManager { return new PostgreSQLSchemaManager($connection, $this); } } ``` Extend PostgreSQLSchemaManager: ``` use App\Shared\Infrastructure\Framework\Doctrine\DBAL\Platforms\PostgreSQLPlatform; use Doctrine\DBAL\Schema\PostgreSQLSchemaManager as BasePostgreSQLSchemaManager; /** * Important: * This class is needed to keep doctrine from generating code that removes our search index, every time we generate a * migration. Doctrine does not support custom indexes. We need to extend doctrine to implement this functionality. * We can remove this class, but we would need to very careful not to delete our search index every time we generate * a migration. */ class PostgreSQLSchemaManager extends BasePostgreSQLSchemaManager { /** * This method is needed to keep doctrine from removing the (custom) search index when we generate a migration. */ protected function _getPortableTableIndexesList($tableIndexes, $tableName = null) { $indexes = parent::_getPortableTableIndexesList($tableIndexes, $tableName); foreach ($indexes as $indexName => $index) { if (str_starts_with($indexName, PostgreSQLPlatform::TEXT_SEARCH_INDEX_PREFIX)) { unset($indexes[$indexName]); } } return $indexes; } } ``` Configuration in packages/doctrine.yaml: ``` doctrine: dbal: platform_service: App\Shared\Infrastructure\Framework\Doctrine\DBAL\Platforms\PostgreSQLPlatform schema_manager_factory: App\Shared\Infrastructure\Framework\Doctrine\DBAL\Platforms\PostgreSQLPlatform ```

admin commented

2026-01-22 15:15:46 +01:00

@boedy commented on GitHub (Oct 25, 2024):

The proposed solution seems unnecessarily complex for such a straightforward requirement. All that’s being asked is to define the index type, which is essentially just a string.

Sure, different databases have unique index types, but requiring complex workarounds for this seems excessive. Why not keep it simple for specifying these index types, so users can just set them directly without extra configuration? 🤷‍♂️


// KISS 💋 

/**
 * @Entity
 * @Table(name="example_table", indexes={
 *     @Index(name="example_gin_index", columns={"example_column"}, options={"type": "gin"})
 * })
 */
class ExampleEntity
{
}

@boedy commented on GitHub (Oct 25, 2024): The proposed solution seems unnecessarily complex for such a straightforward requirement. All that’s being asked is to define the index type, which is essentially just a string. Sure, different databases have unique index types, but requiring complex workarounds for this seems excessive. Why not keep it simple for specifying these index types, so users can just set them directly without extra configuration? 🤷‍♂️ ```php // KISS 💋 /** * @Entity * @Table(name="example_table", indexes={ * @Index(name="example_gin_index", columns={"example_column"}, options={"type": "gin"}) * }) */ class ExampleEntity { } ```

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5723