DdlUtils
  1. DdlUtils
  2. DDLUTILS-208

Recreating a database does not start the new sequence at the value of the old table

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 1.3
    • Labels:
      None
    • Environment:
      PostgreSQL 8.3.1

      Description

      PlatformImplBase.processChange(RecreateTableChange) does not migrate (autoincrement) sequences, but does call SqlBuilder.dropTables. PostgreSqlBuilder.dropTables explicitly drops the sequences:
      for (int idx = 0; idx < columns.length; idx++)

      { dropAutoIncrementSequence(table, columns[idx]); }

      (On the other hand, HsqlDbBuilder does not drop the sequence for instance.)
      The sequence is created again, but that causes 'duplicate' errors on the column using the sequence.

        Issue Links

          Activity

          Hide
          Rijk van Haaften added a comment -

          Dropping works fine indeed. My autoincrement column is also primary key. Lets say it contains 5 items: 0,1,2,3,4 Now due to a RecreateTableChange, the sequence and the table are dropped, then newly created and the data of the table is restored. But the sequence is not restored. The new sequence starts at 0 (again). Now creation of a new row fails because primary key 0 already exists. The sequence needs to be restored to 4 (last_value = 4) so creation of new rows goes on from 5 up.

          Show
          Rijk van Haaften added a comment - Dropping works fine indeed. My autoincrement column is also primary key. Lets say it contains 5 items: 0,1,2,3,4 Now due to a RecreateTableChange, the sequence and the table are dropped, then newly created and the data of the table is restored. But the sequence is not restored. The new sequence starts at 0 (again). Now creation of a new row fails because primary key 0 already exists. The sequence needs to be restored to 4 (last_value = 4) so creation of new rows goes on from 5 up.
          Hide
          Thomas Dudziak added a comment -

          I'm not sure I understand the problem. RecreateTableChange will cause the table to be recreated, and the order of DROP TABLE and DROP SEQUENCE statements makes sure that the table id dropped before the sequence, so how could that cause errors on columns using the sequence ?

          Show
          Thomas Dudziak added a comment - I'm not sure I understand the problem. RecreateTableChange will cause the table to be recreated, and the order of DROP TABLE and DROP SEQUENCE statements makes sure that the table id dropped before the sequence, so how could that cause errors on columns using the sequence ?

            People

            • Assignee:
              Thomas Dudziak
              Reporter:
              Rijk van Haaften
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development