DBAL-50: PgSQL driver does not create indexes on foreign key columns #1703

Closed
doctrinebot opened this Issue Aug 18, 2010 · 6 comments

2 participants

@doctrinebot

Jira issue originally created by user petr_motejlek:

The PostgreSQL database does not create indexes for foreign key columns, the user has to create them by hand. I think that indexes for foreign keys should be created automatically... On my system, an index will not be created automatically for the group_id column in the user table.

/****
 * @Entity
 */
class User {
    /****
     * @ManyToOne(targetEntity="Group", inversedBy="users")
     */
    protected $group;
}

/****
 * @Entity
 */
class Group {
    /****
     * @OneToMany(targetEntity="User", mappedBy="group")
     */
    protected $users;

    public function **construct() {
        $this->users = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

I am using current git clone and PgSQL 8.4.

@doctrinebot

Comment created by petr_motejlek:

I'd just like to add that there's an even worse problem with this -- the indices are not created even for tables that Doctrine creates automatically -- for example the joining tables...

@doctrinebot

Comment created by @beberlei:

i'll look into it.

@doctrinebot

Comment created by @beberlei:

Fixed in master, leading to several follow up bugs that all had to be fixed:

  1. generate identifier allowed first char to be a number
  2. postgresql composite foreign key detection left a space in the second (and more) column names
  3. Index column names were not sanitized to lower-case, leading to comparison bugs.

There has been a major refactoring now such that, for each foreign key there is always an explicit index being created. On SQLite, Postgres and Oracle this can lead to quite some additional indexes being created now using SchemaTool --update. MySQL already did this implicitly.

There are now heuristics that detect duplicate indexes (based on columns indexed) and override rules (adding primary on columns foo, bar will delete index on columns foo bar).

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by @beberlei:

Note, this commit will not get into Doctrine ORM master unless you update the git-submodule explicitly:

cd lib/vendor/doctrine-dbal
git checkout master

For RC-1 this will be visible to the ORM trunk/master also.

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.0.0-RC1-RC3 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment