Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-50

PgSQL driver does not create indexes on foreign key columns

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-BETA4
    • Fix Version/s: 2.0.0-RC1-RC3
    • Component/s: Schema Managers
    • Labels:
      None

      Description

      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.

        Issue Links

          Activity

          Petr Motejlek created issue -
          Petr Motejlek made changes -
          Field Original Value New Value
          Description 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.
          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.

          <?php
          /**
           * @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.
          Petr Motejlek made changes -
          Description 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.

          <?php
          /**
           * @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.
          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.

          {code:borderStyle=solid}
          /**
           * @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();
              }
          }
          {code}

          I am using current git clone and PgSQL 8.4.
          Roman S. Borschel made changes -
          Assignee Roman S. Borschel [ romanb ] Benjamin Eberlei [ beberlei ]
          Roman S. Borschel made changes -
          Fix Version/s 2.0-RC1 [ 10091 ]
          Hide
          Petr Motejlek added a comment -

          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...

          Show
          Petr Motejlek added a comment - 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...
          Hide
          Benjamin Eberlei added a comment -

          i'll look into it.

          Show
          Benjamin Eberlei added a comment - i'll look into it.
          Benjamin Eberlei made changes -
          Project Doctrine 2 - ORM [ 10032 ] Doctrine DBAL [ 10040 ]
          Key DDC-756 DBAL-50
          Affects Version/s 2.0.0-BETA4 [ 10071 ]
          Affects Version/s 2.0-BETA3 [ 10060 ]
          Affects Version/s 2.0-BETA4 [ 10072 ]
          Component/s Schema Managers [ 10057 ]
          Component/s Tools [ 10015 ]
          Fix Version/s 2.0-RC1 [ 10091 ]
          Benjamin Eberlei made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Hide
          Benjamin Eberlei added a comment -

          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).

          Show
          Benjamin Eberlei added a comment - 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).
          Benjamin Eberlei made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Fix Version/s 2.0.0-RC1 [ 10094 ]
          Resolution Fixed [ 1 ]
          Hide
          Benjamin Eberlei added a comment -

          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.

          Show
          Benjamin Eberlei added a comment - 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.
          Benjamin Eberlei made changes -
          Workflow jira [ 11800 ] jira-feedback2 [ 17658 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback2 [ 17658 ] jira-feedback3 [ 20013 ]
          Steve Müller made changes -
          Link This issue is referenced by DBAL-1063 [ DBAL-1063 ]
          Marco Pivetta made changes -
          Link This issue is referenced by DDC-3478 [ DDC-3478 ]

          This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

          • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-50, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

            People

            • Assignee:
              Benjamin Eberlei
              Reporter:
              Petr Motejlek
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: