Bug 358956: [PostgreSQL] Sequences need to be renamed when their field is renamed

Patch By Max Kanat-Alexander <mkanat@bugzilla.org> (module owner) a=myk

2 files changed, 29 insertions, 2 deletions

diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index 98bfc5903..ff596ad0d 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -207,6 +207,15 @@ sub bz_unlock_tables {
     }
 }
 
+# Tell us whether or not a particular sequence exists in the DB.
+sub bz_sequence_exists {
+    my ($self, $seq_name) = @_;
+    my $exists = $self->selectrow_array(
+        'SELECT 1 FROM pg_statio_user_sequences WHERE relname = ?',
+        undef, $seq_name);
+    return $exists || 0;
+}
+
 #####################################################################
 # Custom Database Setup
 #####################################################################
@@ -236,6 +245,16 @@ sub bz_setup_database {
     _fix_case_differences('products', 'name');
     $self->bz_add_index('products', 'products_name_lower_idx',
         {FIELDS => ['LOWER(name)'], TYPE => 'UNIQUE'});
+
+    # bz_rename_column didn't correctly rename the sequence.
+    if ($self->bz_column_info('fielddefs', 'id')
+        && $self->bz_sequence_exists('fielddefs_fieldid_seq')) 
+    {
+        print "Fixing fielddefs_fieldid_seq sequence...\n";
+        $self->do("ALTER TABLE fielddefs_fieldid_seq RENAME TO fielddefs_id_seq");
+        $self->do("ALTER TABLE fielddefs ALTER COLUMN id
+                    SET DEFAULT NEXTVAL('fielddefs_id_seq')");
+    }
 }
 
 # Renames things that differ only in case.
diff --git a/Bugzilla/DB/Schema/Pg.pm b/Bugzilla/DB/Schema/Pg.pm
index 21b0c6d96..0101a1e43 100644
--- a/Bugzilla/DB/Schema/Pg.pm
+++ b/Bugzilla/DB/Schema/Pg.pm
@@ -92,8 +92,16 @@ sub _initialize {
 
 sub get_rename_column_ddl {
     my ($self, $table, $old_name, $new_name) = @_;
-
-    return ("ALTER TABLE $table RENAME COLUMN $old_name TO $new_name");
+    my @sql = ("ALTER TABLE $table RENAME COLUMN $old_name TO $new_name");
+    my $def = $self->get_column_abstract($table, $old_name);
+    if ($def->{TYPE} =~ /SERIAL/i) {
+        # We have to rename the series also, and fix the default of the series.
+        push(@sql, "ALTER TABLE ${table}_${old_name}_seq 
+                      RENAME TO ${table}_${new_name}_seq");
+        push(@sql, "ALTER TABLE $table ALTER COLUMN $new_name 
+                    SET DEFAULT NEXTVAL('${table}_${new_name}_seq')");
+    }
+    return @sql;
 }
 
 sub _get_alter_type_sql {