PostgreSQL uses sequences to track the current value of a serial column. When a serial column is created, a sequence is automatically created with a naming convention of {table_name}_{column_name}_seq. However, this sequence doesn't get renamed when a column is renamed. Therefore, a sequence belonging to a serial column that is changed by the schema API will no longer be accessible.

For example, I take a serial column called "test_column" and rename it to "test_column_old". The serial will still be called "test_column_old". Then I create a new serial column called "test_column". PostgreSQL will create a new sequence called "{table_name}_test_column_seq1" because "{table_name}_test_column_seq" already exists. This means, when Drupal tries to access that columns sequence, it will access what the schema API knows and not the correct sequence for either columns.

I think we need to add checks to move/delete sequences along with serial columns.

Comments

andypost’s picture

damien tournoud’s picture

#761976 doesn't explicitly cover renaming serial columns. Maybe we should broaden the scope of this other issue.

chx’s picture

Priority: Critical » Normal

I do not see where Drupal is broken critically here. When was the last time we changed a serial field? (Aside from changing uid to be int not a serial.)

damien tournoud’s picture

Agreed on the non-criticality of this. Would be good to expand the test coverage of this, anyway.

gnuget’s picture

Version: 7.x-dev » 8.8.x-dev
Component: postgresql database » postgresql db driver
Issue summary: View changes

While working on #3024410: Watchdog wid column should be unsigned we faced a new problem, basically, we just wanted to make the watchdog's wid column unsigned so we used Database::getConnection()->schema()->changeField() and the driver tried to recreated the secuence but we didn't change the name of the table so the sequence already existed and it failed because it tried to create it again.

vacho’s picture

This issue is related to this #3026290 PostgreSQL constraints are still not renamed properly on table renames

Reviewing the patch we will be able to find the way to end this problem with postgreSQL. In advance I found this code at
/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php: 603


if (!empty($info->serial_fields)) {
      foreach ($info->serial_fields as $field) {
        // The initial name of the sequence is generated automatically by
        // PostgreSQL when the table is created, so we need to use
        // pg_get_serial_sequence() to retrieve it.
        $old_sequence = $this->connection->query("SELECT pg_get_serial_sequence('" . $old_full_name . "', '" . $field . "')")->fetchField();

        // If the new sequence name exceeds the maximum identifier length limit,
        // it will not match the pattern that is automatically applied by
        // PostgreSQL on table creation, but that's ok because
        // pg_get_serial_sequence() will return our non-standard name on
        // subsequent table renames.
        $new_sequence = $this->ensureIdentifiersLength($new_name, $field, 'seq', '_');

        $this->connection->query('ALTER SEQUENCE ' . $old_sequence . ' RENAME TO ' . $new_sequence);
      }
    }
    // Now rename the table.
    $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO ' . $prefixInfo['table']);
    $this->resetTableInformation($table);
gnuget’s picture

Status: Active » Postponed
Related issues: +#3028706: New serial columns do not own sequences

Thanks, Vacho for your help.

I was working on this today and in order to use pg_get_serial_sequence() to drop the sequence (before re-create the new one) we need to fix #3028706: New serial columns do not own sequences first because right now it returns empty and it does not find the correct sequence.

So... I'm going to postpone this one temporary.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

daffie’s picture

Status: Postponed » Closed (duplicate)

Closing this issue as a duplicate of #3028706: New serial columns do not own sequences.