search

doctrine / dbal

Doctrine Database Abstraction Layer
https://www.doctrine-project.org/projects/dbal.html
MIT License
9.44k stars 1.33k forks source link

Doctrine ORM tries to recreate partial indexes with the same definition every time. #3780

Open skobkin opened 7 years ago

skobkin commented 7 years ago

I've just encountered strange behavior when Doctrine ORM 2.5.5 tries to recreate some of indexes each time when I'm generating the migration or using doctrine:schema:update in my project.

I have following indexes definition:

/**
 * @ORM\Table(name="telegram_accounts", schema="users", indexes={
 *      @ORM\Index(name="subscriber_notification_idx", columns={"subscriber_notification"}, options={"where": "subscriber_notification = TRUE"}),
 *      @ORM\Index(name="rename_notification_idx", columns={"rename_notification"}, options={"where": "rename_notification = TRUE"}),
 * })
 * @ORM\Entity(repositoryClass="Skobkin\Bundle\PointToolsBundle\Repository\Telegram\AccountRepository")
 * @ORM\HasLifecycleCallbacks()
 */
class Account

Full entity code

Every time I do that it generates following SQL:

-- You can also see that index names are generated without schema name which is other bug (I think it's known)
DROP INDEX rename_notification_idx;
DROP INDEX subscriber_notification_idx;
CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE;
CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE;

Even if I apply this migration or do doctrine:schema:update --force it will do that next time again.

My stack:

My project's config.yml

Ocramius commented 7 years ago

@skobkin does this come up also with 2.5.4?

skobkin commented 7 years ago

@Ocramius Yes. I've just downgraded doctrine/orm to 2.5.4

  - Removing doctrine/orm (v2.5.5)
  - Installing doctrine/orm (v2.5.4)

Then generated new migration with following code:

    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        // I've added 'users' schema here manually to prevent migration fail
        $this->addSql('DROP INDEX users.rename_notification_idx');
        $this->addSql('DROP INDEX users.subscriber_notification_idx');
        $this->addSql('CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE');
        $this->addSql('CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE');
    }

Then I've done doctrine:migration:migrate, then doctrine:schema:update --dump-sql and here it is:

$ sf doc:sche:up --dump-sql
DROP INDEX subscriber_notification_idx;
DROP INDEX rename_notification_idx;
CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE;
CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE;
Ocramius commented 7 years ago

@skobkin so ORM version is not causing this? Can you check with different DBAL and ORM versions to see if this is a regression?

skobkin commented 7 years ago

@Ocramius Yes I can. Which versions you think will be more efficient to try?

Ocramius commented 7 years ago

@skobkin I'd try 2.4.latest of both ORM and DBAL, then 2.5.0 of both. If the bug is reproducible in 2.4.0, then this is a new bug, not a regression.

skobkin commented 7 years ago

doctrine/orm 2.4.8, doctrine/dbal 2.4.5 - can't reproduce due to lack of options support in @Index annotation:

$ sf doc:sche:up --dump-sql
...                                                                                                                                                          
  [Doctrine\Common\Annotations\AnnotationException]                                                                                                                                                   
  [Creation Error] The annotation @ORM\Index declared on class Skobkin\Bundle\PointToolsBundle\Entity\Telegram\Account does not have a property named "options". Available properties: name, columns

Changing doctrine/orm and doctrine/dbal both to 2.5.0 forced me to use --with-dependencies:

  - Removing doctrine/annotations (v1.3.0)
  - Installing doctrine/annotations (v1.3.1)
    Downloading: 100%         

  - Removing doctrine/common (v2.6.2)
  - Installing doctrine/common (v2.5.3)
    Loading from cache

  - Removing doctrine/dbal (v2.5.5)
  - Installing doctrine/dbal (v2.5.0)
    Downloading: 100%         

  - Removing doctrine/orm (v2.5.6)
  - Installing doctrine/orm (v2.5.0)
    Downloading: 100%

After that I generated new migration:

// Seems like 2.5.0 in comparison with 2.5.5 have some problems with named indexes (I also generated migration which renamed IDX_SOME_HASH to human readable names after some of composer updates recently)
// https://bitbucket.org/skobkin/point-tools/src/fd3cd2d5171e043ab8011890d89eaf63bd6504d1/app/DoctrineMigrations/Version20170108152129.php?at=master&fileviewer=file-view-default
$this->addSql('ALTER INDEX subscriptions.author_idx RENAME TO IDX_22DA64DDF675F31B');
$this->addSql('ALTER INDEX subscriptions.subscriber_idx RENAME TO IDX_22DA64DD7808B1AD');

// The problem is still here:
$this->addSql('DROP INDEX subscriber_notification_idx');
$this->addSql('DROP INDEX rename_notification_idx');
$this->addSql('CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE');
$this->addSql('CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE');

After execution of this migration doc:sche:up --dump-sql returns this:

DROP INDEX subscriber_notification_idx;
DROP INDEX rename_notification_idx;
CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE;
CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE;

Seems like the problem was introduced with @Index(options={}) support.

Ocramius commented 7 years ago

Hmm, wondering if DBAL is able to extract column options from an existing DB in MySQL...

andreasferber commented 7 years ago

Try putting parentheses around your partial index WHERE clause:

@ORM\Index(name="subscriber_notification_idx", columns={"subscriber_notification"}, options={"where": "(subscriber_notification = TRUE)"}),

The way getListTableIndexesSQL() in Doctrine\DBAL\Platform\PostgreSqlPlatform queries the PostgreSQL system tables leads to those parentheses being added if they aren't already there, which makes the schema tool notice a difference.

CvekCoding commented 6 years ago

Had the same issue. Solution is to put paretheses around WHERE clause (as andreasferber suggested) + use lower-case "true". So the proper string will be:

@ORM\Index(name="subscriber_notification_idx", columns={"subscriber_notification"}, options={"where": "(subscriber_notification = true)"}),

elfin-sbreuers commented 6 years ago

I observe the same issue without using Index explicitly. I am using doctrine in a symfony project with a sqlite database. The following entities with a OneToMany relationship are created:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\LegRepository")
 */
class Leg
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Dog", inversedBy="leg")
     */
    private $dog;
}
<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\DogRepository")
 */
class Dog
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Leg", mappedBy="dog")
     */
    private $legs;

    public function __construct()
    {
        $this->legs = new ArrayCollection();
    }
}

Executing 

./bin/console doctrine:schema:update --dump-sql

does always create the following output:

     DROP INDEX IDX_75D0804F634DFEB;
     CREATE TEMPORARY TABLE __temp__leg AS SELECT id, dog_id FROM leg;
     DROP TABLE leg;
     CREATE TABLE leg (id INTEGER NOT NULL, dog_id INTEGER DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_75D0804F634DFEB FOREIGN KEY (dog_id) REFERENCES dog (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
     INSERT INTO leg (id, dog_id) SELECT id, dog_id FROM __temp__leg;
     DROP TABLE __temp__leg;
     CREATE INDEX IDX_75D0804F634DFEB ON leg (dog_id);

independent of the state of the database. This also spoiles the doctrine:migrations of my symfony project. I observed the issue with doctrine/dbal v2.7.1 and v2.5.13. Symfony in version 3.3.16 and 4.0.9 are both affected.

Could you give me a hint of how to address this issue? Since I don't see how the annotation syntax could help in this case.

elfin-sbreuers commented 6 years ago

I withdraw my comment. If somebody else observes this issue:

It is based on the fact that dbal removed foreign key support for sqlite platform.

hacfi commented 5 years ago

I've come across the same issue: Stack: PHP 7.2.12 MySQL 5.7.21 doctrine/dbal v2.9.1 doctrine/orm v2.6.3 symfony/symfony v4.2.1

...
 * @ORM\Table(
 *     uniqueConstraints={
 *         @ORM\UniqueConstraint(name="IDX_UNIQ_CUSTADDR_FKCUST_DEFBILLADDR", columns={"fk_customer"}, options={"where": "
 * (is_default_billing_address = true)"}),
 *         @ORM\UniqueConstraint(name="IDX_UNIQ_CUSTADDR_FKCUST_DEFSHIPADDR", columns={"fk_customer"}, options={"where": "
 * (is_default_shipping_address = true)"})
 *     }
 * )
...
hacfi commented 5 years ago

False alarm in my case: I used this on a previous project with Postgresql and trying it with MySQL now which doesn't support partial indexes. The schema tool compares the index coming from the db that doesn't have a where clause with the index generated from the mapping which has a where clause.

tasselchof commented 5 years ago

I have an issue with index:

rate_modifier_id = 3 AND state NOT IN ('new')

doctrine/dbal v2.9.2 doctrine/orm v2.6.3

trickeyone commented 5 years ago

I've been getting this issue for a while (since 2015), can't give a specific version, but whatever the version was in 2015. It also occurs for foreign keys. It will add drops for FKs that haven't been changed.

Edit: For defined Indexes in @Table, I've seen the issue when not specifying a where condition

OO00O0O commented 5 years ago

Having same problem.

doctrine/annotations                v1.6.1   
doctrine/cache                      v1.8.0   
doctrine/collections                v1.6.1   
doctrine/common                     v2.10.0  
doctrine/data-fixtures              v1.3.1   
doctrine/dbal                       v2.9.2   
doctrine/doctrine-bundle            1.10.2   
doctrine/doctrine-cache-bundle      1.3.5    
doctrine/doctrine-fixtures-bundle   3.1.0    
doctrine/doctrine-migrations-bundle v1.3.2   
doctrine/event-manager              v1.0.0   
doctrine/inflector                  v1.3.0   
doctrine/instantiator               1.2.0    
doctrine/lexer                      v1.0.1   
doctrine/migrations                 v1.8.1   
doctrine/orm                        v2.6.3   
doctrine/persistence                1.1.1    
doctrine/reflection                 v1.0.0
$this->addSql('ALTER TABLE employee_schedule_breaks DROP FOREIGN KEY FK_4F7C782A8C03F15CAA9E377A');
$this->addSql('DROP INDEX IDX_4F7C782AAA9E377A8C03F15C ON employee_schedule_breaks');
$this->addSql('ALTER TABLE 
          employee_schedule_breaks 
        ADD 
          CONSTRAINT FK_4F7C782A8C03F15CAA9E377A FOREIGN KEY (employee_id, date) REFERENCES employee_schedule (employee_id, date) ON DELETE CASCADE');
/**
 * @ORM\Entity
 * @ORM\Table(name="employee_schedule_breaks")
 */
class EmployeeScheduleEntryBreak
{
    /**
     * @ORM\ManyToOne(targetEntity="EmployeeScheduleEntry", inversedBy="breaks")
     * @ORM\JoinColumns({
     *      @ORM\JoinColumn(name="employee_id", referencedColumnName="employee_id", onDelete="CASCADE"),
     *      @ORM\JoinColumn(name="date", referencedColumnName="date", onDelete="CASCADE"),
     * })
     * @var EmployeeScheduleEntry
     */
    private $entry;

    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="xxx\EmployeeBundle\Entity\Employee")
     * @ORM\JoinColumn(name="employee_id", nullable=false, onDelete="CASCADE")
     * @var Employee
     */
    private $employee;

    /**
     * @ORM\Column(name="date", type="date", nullable=false)
     * @var Date
     */
    private $date;
}
/**
 * @ORM\Entity(repositoryClass="xxx\CalendarBundle\Entity\Repository\EmployeeScheduleEntryRepository")
 * @ORM\Table(name="employee_schedule")
 */
class EmployeeScheduleEntry
{
    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="xxx\EmployeeBundle\Entity\Employee")
     * @ORM\JoinColumn(name="employee_id", nullable=false, onDelete="CASCADE")
     * @var Employee
     */
    private $employee;

    /**
     * @ORM\Column(name="date", type="date", nullable=false)
     * @ORM\Id
     * @var Date
     */
    private $date;
}
lyrixx commented 5 years ago

Same issue here. With

kocur1508 commented 5 years ago

Had a same issue with droping and creating index. Adding parentheses worked just fine for me.

options={"where": "(current = true)"}

bmxpapr commented 4 years ago

Same issue here with the following stack:

- PHP 7.2.19-1+ubuntu16.04.1+deb.sury.org+1 (cli) (built: May 31 2019 11:16:55) ( NTS )
- mysqld  Ver 5.6.43 for Linux on x86_64 (MySQL Community Server (GPL))
- symfony/symfony                     v4.2.11
- doctrine/annotations                v1.8.0
- doctrine/cache                      v1.8.1
- doctrine/collections                v1.6.2
- doctrine/common                     v2.11.0
- doctrine/dbal                       v2.9.2
- doctrine/doctrine-bundle            1.9.1
- doctrine/doctrine-cache-bundle      1.3.3
- doctrine/doctrine-migrations-bundle v2.0.0
- doctrine/event-manager              v1.0.0
- doctrine/inflector                  v1.3.0
- doctrine/instantiator               1.2.0
- doctrine/lexer                      1.1.0
- doctrine/migrations                 2.1.1
- doctrine/orm                        v2.6.4
- doctrine/persistence                1.1.1
- doctrine/reflection                 v1.0.0

I also confirm that reverting doctrine/dbal to 2.9.1 solves the issue, so this suggets the bug was introduced in 2.9.2. I also tried uprading to 2.9.3 and 2.10.0 but the issue is still there.

yvoyer commented 4 years ago

I made a PR to reproduce the issue in DBAL. https://github.com/doctrine/dbal/pull/3458 I'll check if it is still a WIP or not.

ostrolucky commented 4 years ago

Closing here since it's DBAL and not ORM issue.

lyrixx commented 4 years ago

I guess we can all use https://github.com/doctrine/dbal/issues/2866 to track progression on this issue

lyrixx commented 4 years ago

@ostrolucky Could you move this issue to the DBAL repo ? thanks

henrypenny commented 4 years ago

I had a similar issue with a key.

I debugged in Doctrine\DBAL\Schema\Comparitor::diffTable to find out the difference between $fromSchema and $toSchema

Before:

@ORM\Index(columns={"text"}, options={"lengths": {100}})

After:

@ORM\Index(columns={"text"}, options={"lengths": {"100"}})

Once I added quotations around 100 the from and to schema's matched and the diff no longer dropped and recreated the key everytime.

PowerKiKi commented 4 years ago

In this (three year old) thread different people had different problems and solutions. But AFAIK nobody were able to clearly identify a bug in DBAL. Instead the problems are the result of a misuse of (a slightly tricky part of) the lib.

So I'm in favor to close this issue. And re-open a new issue only if somebody can clearly identify and reproduce a bug.

For the record possible solutions for the end-users are:

henrypenny commented 4 years ago

In the case of my issue: The parentheses should not be required as the value is an integer. However, making this change would likely be a breaking change. I would like to see the parenthesis be made optional - or for an error to be thrown if they are omitted.

leimd commented 4 years ago

We are running into the same issue with Postgres 9.6 as well. In our case, we are building partial index like this:

*     @ORM\Index(
*          name="monetaryitem_orgunit_idx",
*          columns={"orgunit_id"},
*          options={
*              "where": "(type IN ('invoice', 'purchaseorder', 'beanpayment'))"
*          }
*     )

And everytime we generate the migration script, it would ask us to drop the old index and create new index. After putting some var_dump statement in lib/Doctrine/DBAL/Schema/Index.php::samePartialIndex, we've found out that the where clause in the annotation and the where clause in the actual database is acutally different, the where clause in database is converted to something like this CREATE INDEX monetaryitem_orgunit_created_idx ON public.monetaryitem USING btree (created, orgunit_id) WHERE ((type)::text = ANY ((ARRAY['invoice'::character varying, 'importedpayment'::character varying, 'purchaseorder'::character varying])::text[]))

and the result from our var_dumps looks like this:

/var/bean/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/Index.php:213:
string(72) "This index option: (type IN ('invoice', 'purchaseorder', 'beanpayment'))"
/var/bean/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/Index.php:214:
string(158) "Other index option: ((type)::text = ANY ((ARRAY['invoice'::character varying, 'purchaseorder'::character varying, 'beanpayment'::character varying])::text[]))"

This has also been verified by manually create the index, and afterwards the indexdef in pg_indexes table is automatically changed by Postgres.

glynnforrest commented 3 years ago

Here are some working examples for nullable datetime columns:

@ORM\UniqueConstraint(columns={"email", "user_id"}, options={"where": "(deleted_at IS NULL)"})

Multiple columns need another layer of brackets:

@ORM\UniqueConstraint(columns={"email", "account_id"}, options={"where":"((deleted_at IS NULL) AND (accepted_at IS NULL))"})

Note the uppercase keywords (looks like the lowercase booleans mentioned here act differently).

svitiashchuk commented 3 years ago

I had the similar experience with partial index and Postgres (Postgres 12.6, doctrine/dbal 2.13.1, doctrine/orm 2.9.2). So here is example how I resolved this issue in my case when i had to use predicate on text column:

Annotation:

@ORM\Index(
    name="recommended",
    columns={"recommendation_till"},
    options={"where": "((approved = true) AND ((status)::text = 'active'::text))"}
)

Generated SQL in migration generated with `doctrine:migrations:diff:

CREATE INDEX recommended ON article (recommendation_till) 
WHERE ((approved = true) AND ((status)::text = \'active\'::text))

Running doctrine:migrations:diff second time will not try to recreate indexes 👌

BonBonSlick commented 7 months ago

Hello, another question, does PGSQL support partial indexing with doctrine?

We have the same scenario which seems doesnt work

<entity name="App\Domain\UserPack\Email\Model\UserEmail"
        repository-class="App\Infrastructure\Persistence\Repository\DB\DQL\UserEmail\DQLRepositoryUserEmail">
    <!-- Indexes -->
    <indexes>
        <index name="user_email_search_index" columns="email"/>
    </indexes>

    <unique-constraints>
        <unique-constraint
                name="unique_email_for_user"
                columns="user_uuid,email"
        />
        <unique-constraint
                name="user_only_one_active_fallback_and_main_email"
                columns="user_uuid,is_enabled,is_fallback"
        >
            <!--                columns="user_uuid,is_enabled,is_fallback"-->
            <!--                columns="user_uuid,email,is_enabled,is_fallback"-->
            <options>
                <option name="user_should_have_only_one_unique_email_option">
                    (((id IS NOT NULL) AND (name IS NULL)) AND (email IS NULL))
                </option>
                <!--                    WHERE user_uuid AND email-->
                <option name="user_should_have_only_one_enabled_main_email_option">
                    (user_uuid AND is_enabled AND is_fallback)
                </option>
                <option name="user_should_have_only_one_enabled_main_email_option">
                    (user_uuid AND is_enabled AND NOT is_fallback)
                </option>

                <option name="user_only_one_active_fallback_and_main_email_option">
                    ((is_enabled AND is_fallback) AND (is_enabled AND NOT is_fallback))
                </option>
            </options>
        </unique-constraint>
    </unique-constraints>

    <embedded name="email"
              class="App\Domain\Core\ValueObjects\String\Email"
              use-column-prefix="false"
    />
    <embedded name="isEnabled"
              class="App\Domain\Core\ValueObjects\Boolean\IsEnabled"
              use-column-prefix="false"
    />
    <embedded name="isFallback"
              class="App\Domain\Core\ValueObjects\Boolean\IsFallback"
              use-column-prefix="false"
    />

tried lots of ways e.g.

<unique-constraints>
            <unique-constraint
                    name="unique_email_for_user"
                    columns="user_uuid,email"
            />
            <unique-constraint
                    name="user_only_one_active_fallback_and_main_email"
                    columns="user_uuid,is_enabled,is_fallback"
            >
                <!--                columns="user_uuid,is_enabled,is_fallback"-->
                <!--                columns="user_uuid,email,is_enabled,is_fallback"-->
                <options>
                    <option name="user_should_have_only_one_unique_email_option">
                        (( (user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE) ))
                    </option>
                    <option name="user_should_have_only_one_unique_email_option">
                        (( (user_uuid IS NOT NULL) AND (is_enabled IS true)) AND (is_fallback IS true) ))
                    </option>
                    <option name="user_should_have_only_one_unique_email_option">
                        (((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
                    </option>
                    <option name="user_should_have_only_one_unique_email_option">
                        WHERE: (((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
                    </option>
                    <option name="user_should_have_only_one_unique_email_option">
                        <option name="WHERE">
                            (((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
                        </option>
                    </option>
                    <option name="WHERE">
                        WHERE:(((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
                    </option>
                    <!--                    WHERE user_uuid AND email-->
                    <option name="user_should_have_only_one_enabled_main_email_option">
                        (user_uuid AND is_enabled AND is_fallback)
                    </option>
                    <option name="user_should_have_only_one_enabled_main_email_option">
                        (user_uuid AND is_enabled AND NOT is_fallback)
                    </option>

                    <option name="user_only_one_active_fallback_and_main_email_option">
                        ((is_enabled AND is_fallback) AND (is_enabled AND NOT is_fallback))
                    </option>
                </options>
            </unique-constraint>
        </unique-constraints>

doctrine does not generate new SQL if option is added or removed. Only default UNIQUE index generated in case its added / removed

$this->addSql('CREATE UNIQUE INDEX user_only_one_active_fallback_and_main_email ON user_email (user_uuid, is_enabled, is_fallback)');
pg_config --version
PostgreSQL 13.13 (Debian 13.13-0+deb11u1)

    "beberlei/doctrineextensions": "^v1.2.8",
    "doctrine/collections": "^1.6",
    "doctrine/common": "^3.3.0",
    "doctrine/dbal": "^2.11",
    "doctrine/doctrine-bundle": "^2.2",
    "doctrine/doctrine-migrations-bundle": "^3.0",
    "doctrine/orm": "^2.7.4",

Thanks in advance!

BonBonSlick commented 7 months ago

We expect doctrine to generate these

CREATE UNIQUE INDEX user_only_one_active_fallback_and_main_email_true ON user_email (user_uuid) WHERE
    (is_enabled AND is_fallback);
CREATE UNIQUE INDEX user_only_one_active_fallback_and_main_email_false ON user_email (user_uuid) WHERE
    (is_enabled AND (NOT is_fallback));
based on 
  <unique-constraints>
            <unique-constraint
                    name="unique_email_for_user"
                    columns="user_uuid,email"
            />
            <unique-constraint
                    name="user_only_one_active_fallback_and_main_email_option_main"
                    columns="user_uuid"
            >
                <options>
                    <option name="WHERE">
                        (is_enabled AND NOT is_fallback)
                    </option>
                </options>
            </unique-constraint>
            <unique-constraint
                    name="user_only_one_active_fallback_and_main_email_option_fallback"
                    columns="user_uuid"
            >
                <options>
                    <option name="WHERE">
                        (is_enabled AND is_fallback)
                    </option>
                </options>
            </unique-constraint>

But it generates withou option WHERE which is not partial index.

BonBonSlick commented 7 months ago

Also it might be a bug, because adding 2 different constraints on the same field but with different name, the last one constraint overrides all previous constraints for the same single field eg


 <unique-constraint
                    name="user_only_one_active_main_email"
                    columns="user_uuid"
            >
                <options>
                    <option name="WHERE">
                        ((is_enabled AND (NOT is_fallback)))
                    </option>
                </options>
            </unique-constraint>
            <unique-constraint
                    name="user_only_one_active_fallback_email"
                    columns="user_uuid"
            >
                <options>
                    <option name="WHERE">
                        ((is_enabled AND is_fallback))
                    </option>
                </options>
            </unique-constraint>
BonBonSlick commented 7 months ago

Btw, I noticed that same issue, SQL to create partial index now generated each time migrations:diff is run.

 public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP INDEX user_only_one_active_main_email');
        $this->addSql('DROP INDEX user_only_one_active_fallback_email');
        $this->addSql('CREATE UNIQUE INDEX user_only_one_active_main_email ON user_email (user_uuid) WHERE ((is_enabled) AND (NOT is_fallback))');
        $this->addSql('CREATE UNIQUE INDEX user_only_one_active_fallback_email ON user_email (user_uuid) WHERE ((is_enabled) AND (is_fallback))');
    }
 <unique-constraints>
            <unique-constraint
                    name="unique_email_for_user"
                    columns="user_uuid,email"
            />

            <unique-constraint
                    name="user_only_one_active_main_email"
                    columns="user_uuid"
            >
                <options>
                    <option name="where">((is_enabled = true) AND (NOT is_fallback = true))</option>
                </options>
            </unique-constraint>
            <unique-constraint
                    name="user_only_one_active_fallback_email"
                    columns="user_uuid"
            >
                <options>
                    <option name="where">((is_enabled = true) AND (is_fallback = true))</option>
                </options>
            </unique-constraint>
        </unique-constraints>

this is definitely a bug.

Fixed this way:

<unique-constraint
                    name="user_only_one_active_main_email"
                    columns="user_uuid"
            >
                <options>
                    <option name="where">((is_enabled = true) AND (is_fallback = false))</option>
                </options>
            </unique-constraint>
            <unique-constraint
                    name="user_only_one_active_fallback_email"
                    columns="user_uuid"
            >
                <options>
                    <option name="where">((is_enabled = true) AND (is_fallback = true))</option>
                </options>
            </unique-constraint>

DO NOT write smth like, otherwise it will generate each time SQL to create index

```
            <option name="where">((is_enabled = true) AND (NOT is_fallback = true))</option>
                <option name="where">(is_enabled AND (NOT is_fallback))</option>
BonBonSlick commented 7 months ago

1 - parentheses for option 2 - parentheses for each condition if you have AND 3 - lowercase "where" 4 - lower case "true / false" - this and all above may not generate sql at all 5 - do not use NOT, instead use "field = false", with NOT it will generate each time new sql

antoniovj1 commented 2 months ago

Same problem using attributes with 4.0.4 and PostgreSQL.

I fixed it by using this syntax:

- #[ORM\UniqueConstraint(name: 'unique_ean', columns: ['value'], options: ['where' => "type = 'EAN'"])]
+ #[ORM\UniqueConstraint(name: 'unique_ean', columns: ['value'], options: ['where' => "((type)::text = 'EAN'::text)"])]