Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-54

Incorrect sequence dropping in PostgreSQL

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: 2.0.0-RC1-RC3
    • Component/s: Platforms
    • Labels:
      None
    • Environment:
      postgresql 8.4.3, Arch Linux 64-bit

      Description

      Currently, DBAL drops the PostgreSQL sequences using the query:

      DROP SEQUENCE sequencename

      While it is quite correct at the first look, it fails, if the sequence is actually used by a table. Because Doctrine 2 ORM tries to drop the sequences before the tables, it makes impossible to drop a database schema in PostgreSQL due to the following exception:


      $ php53 doctrine.php orm:schema-tool:drop
      Dropping database schema...
      PDOException
      SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence admins_id_seq because other objects depend on it
      DETAIL: default for table admins column id depends on sequence admins_id_seq
      HINT: Use DROP ... CASCADE to drop the dependent objects too.
      /.../Libs/Doctrine/DBAL/Connection.php
      Line 570
      Trace:
      0. PDO::query on line 570
      1. Doctrine\DBAL\Connection::executeQuery on line 484
      2. Doctrine\ORM\Tools\SchemaTool::dropSchema on line 78
      3. Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand::executeSchemaCommand on line 59
      4. Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand::execute on line 159
      5. Symfony\Component\Console\Command\Command::run on line 205
      6. Symfony\Component\Console\Application::doRun on line 117
      7. Symfony\Component\Console\Application::run on line 7


      A solution is simply to add the "CASCADE" keyword at the end of the query.

      Although I encountered this problem on DBAL 2.0-beta4, I checked the most up-to-date code on Git, and the problem is still present there.

        Activity

        Tomasz Jędrzejewski created issue -
        Hide
        Benjamin Eberlei added a comment -

        Would it help to drop sequences after tables? If then i would just move the code blocks.

        Show
        Benjamin Eberlei added a comment - Would it help to drop sequences after tables? If then i would just move the code blocks.
        Hide
        Tomasz Jędrzejewski added a comment -

        In this particular case - yes, it would help. But consider that different database engines may have different dependencies between schema elements and be more or less restrictive, so they may require different order of code blocks. I'd recommend to make a bit deeper investigation here in order not to cause potential problems with other database engines.

        Show
        Tomasz Jędrzejewski added a comment - In this particular case - yes, it would help. But consider that different database engines may have different dependencies between schema elements and be more or less restrictive, so they may require different order of code blocks. I'd recommend to make a bit deeper investigation here in order not to cause potential problems with other database engines.
        Hide
        Benjamin Eberlei added a comment -

        Fixed

        Show
        Benjamin Eberlei added a comment - Fixed
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.0.0-RC1 [ 10094 ]
        Resolution Fixed [ 1 ]
        Hide
        Jon Wadsworth added a comment -

        This issue is happening again with Doctrine 2.2.2 on Postgres 9.1.3. when trying to drio a database I get this message even with --full-database

        > php doctrine.php orm:schema-tool:drop --force --full-database
        Dropping database schema...

        [PDOException]
        SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence policycategory_id_seq because other objects depend on it
        DETAIL: default for table policycategory column id depends on sequence policycategory_id_seq
        HINT: Use DROP ... CASCADE to drop the dependent objects too.

        I would love to help diagnose, just let me know what you need and I will be more than happy to help.

        Show
        Jon Wadsworth added a comment - This issue is happening again with Doctrine 2.2.2 on Postgres 9.1.3. when trying to drio a database I get this message even with --full-database > php doctrine.php orm:schema-tool:drop --force --full-database Dropping database schema... [PDOException] SQLSTATE [2BP01] : Dependent objects still exist: 7 ERROR: cannot drop sequence policycategory_id_seq because other objects depend on it DETAIL: default for table policycategory column id depends on sequence policycategory_id_seq HINT: Use DROP ... CASCADE to drop the dependent objects too. I would love to help diagnose, just let me know what you need and I will be more than happy to help.
        Jon Wadsworth made changes -
        Affects Version/s 2.2.2 [ 10197 ]
        Affects Version/s 2.0.0-BETA4 [ 10071 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 11936 ] jira-feedback2 [ 17667 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17667 ] jira-feedback3 [ 20022 ]
        Hide
        Jon Wadsworth added a comment -

        I forgot to update this for anyone else who might have had my problem. The issue was solved for me when I was reviewing some models. The project was originally intended for MySQL and Generated Value Strategy was not set to Auto. Upon changing it to auto, everything worked correctly.

        Show
        Jon Wadsworth added a comment - I forgot to update this for anyone else who might have had my problem. The issue was solved for me when I was reviewing some models. The project was originally intended for MySQL and Generated Value Strategy was not set to Auto. Upon changing it to auto, everything worked correctly.

        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-54, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Tomasz Jędrzejewski
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: