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
Comment #1
andypostI think this is a duplicate of #761976: DatabaseSchema_pgsql::changeField() is broken
Comment #2
damien tournoud commented#761976 doesn't explicitly cover renaming serial columns. Maybe we should broaden the scope of this other issue.
Comment #3
chx commentedI 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.)
Comment #4
damien tournoud commentedAgreed on the non-criticality of this. Would be good to expand the test coverage of this, anyway.
Comment #5
gnugetWhile 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 thesecuencebut we didn't change the name of the table so the sequence already existed and it failed because it tried to create it again.Comment #6
vacho commentedThis 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
Comment #7
gnugetThanks, 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.
Comment #15
daffie commentedClosing this issue as a duplicate of #3028706: New serial columns do not own sequences.