Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-420

Schema Drop SQL incorrect on PostgreSQL with entities with GeneratedValue(strategy="IDENTITY")

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.2
    • Fix Version/s: 2.3.4
    • Component/s: Schema Managers
    • Security Level: All
    • Environment:
      Symfony 2.1, PHP5.4, PostgreSQL 9.1 on Ubuntu 12.04

      Description

      This problem is probably related to #DBAL-54. However that was closed by the raiser as he changed his Entity model and it went away.

      When schema drop is run the following error occurs:
      Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

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

      The source of this problem is the difference between strategy="IDENTITY" and strategy="SEQUENCE"

      With SEQUENCE doctrine creates the table schema with field type BIGINT and no specified. It then creates a seperate sequence and as far as I can tell takes care of getting and inserting the next id number itself.

      With IDENTITY doctrine creates the table schema with field type BIGSERIAL and no specified default. Now postgres automatically creates a sequence and creates the column with type BIGINT and sets the DEFAULT to the pgpsql statement required to get the nextval from the sequence.

      At this point the two differently configured tables will work successfully and identically, except SEQUENCE tables will only get a correct new ID when run through the doctrine code while IDENTITY tables will get the correct new ID whenever an insert is done to the table.

      Because in the case of an IDENTITY field postgresql creates the field with a default value refering to the sequence the sequence can not be deleted before the table reference is removed.

      For my case I need the IDENTITY fields to work as we have a RADIUS server that needs to insert into one table which is managed and mapped to an entity in Doctrine.

      Swapping the order of DROP TABLE and DROP SEQUENCE commands in Doctrine/DBAL/Schema/Visitor/DropSchemaSqlCollector.php in getQueries() line 159. Does not work as a quick fix. The following error occurs as the sequence is quite correctly be dropped along with the table.

      Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

      SQLSTATE[42P01]: Undefined table: 7 ERROR: sequence "radacct_radacctid_seq" does not exist

        Activity

        Hide
        Adam Ashley added a comment -

        This issue also appears to affect Doctrine_Migrations. Generated migrations try to drop and recreate automatically generated sequences associated with SERIAL fields making a mess of the database.

        Show
        Adam Ashley added a comment - This issue also appears to affect Doctrine_Migrations. Generated migrations try to drop and recreate automatically generated sequences associated with SERIAL fields making a mess of the database.
        Hide
        Benjamin Eberlei added a comment -

        A related Github Pull-Request [GH-289] was opened
        https://github.com/doctrine/dbal/pull/289

        Show
        Benjamin Eberlei added a comment - A related Github Pull-Request [GH-289] was opened https://github.com/doctrine/dbal/pull/289
        Hide
        Adrien Crivelli added a comment -

        @Adam Ashley, could you test whether https://github.com/doctrine/dbal/pull/289 solve your issue ?

        Show
        Adrien Crivelli added a comment - @Adam Ashley, could you test whether https://github.com/doctrine/dbal/pull/289 solve your issue ?
        Hide
        Doctrine Bot added a comment -

        A related Github Pull-Request [GH-289] was closed:
        https://github.com/doctrine/dbal/pull/289

        Show
        Doctrine Bot added a comment - A related Github Pull-Request [GH-289] was closed: https://github.com/doctrine/dbal/pull/289

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Adam Ashley
          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: