From 070c57867d95e4555b5c9ab83a66eb35a3ae2bb4 Mon Sep 17 00:00:00 2001 From: zeripath Date: Thu, 18 Mar 2021 06:06:40 +0000 Subject: Fix postgres ID sequences broken by recreate-table (#15015) * Fix postgres ID sequences broken by recreate-table Unfortunately there is a subtle problem with recreatetable on postgres which leads to the sequences not being renamed and not being left at 0. Fix #14725 Signed-off-by: Andrew Thornton * let us try information_schema instead Signed-off-by: Andrew Thornton * try again Signed-off-by: Andrew Thornton Co-authored-by: techknowlogick Co-authored-by: 6543 <6543@obermui.de> --- models/migrations/migrations.go | 65 ++++++++++++++++++++++++++++++++++++++++- models/migrations/v175.go | 53 +++++++++++++++++++++++++++++++++ 2 files changed, 117 insertions(+), 1 deletion(-) create mode 100644 models/migrations/v175.go (limited to 'models/migrations') diff --git a/models/migrations/migrations.go b/models/migrations/migrations.go index 27c159ee4a..f46d151b7b 100644 --- a/models/migrations/migrations.go +++ b/models/migrations/migrations.go @@ -296,6 +296,8 @@ var migrations = []Migration{ NewMigration("Add time_id column to Comment", addTimeIDCommentColumn), // v174 -> v175 NewMigration("create repo transfer table", addRepoTransfer), + // v175 -> v176 + NewMigration("Fix Postgres ID Sequences broken by recreate-table", fixPostgresIDSequences), } // GetCurrentDBVersion returns the current db version @@ -565,6 +567,31 @@ func recreateTable(sess *xorm.Session, bean interface{}) error { return err } case setting.Database.UsePostgreSQL: + var originalSequences []string + type sequenceData struct { + LastValue int `xorm:"'last_value'"` + IsCalled bool `xorm:"'is_called'"` + } + sequenceMap := map[string]sequenceData{} + + schema := sess.Engine().Dialect().URI().Schema + sess.Engine().SetSchema("") + if err := sess.Table("information_schema.sequences").Cols("sequence_name").Where("sequence_name LIKE ? || '_%' AND sequence_catalog = ?", tableName, setting.Database.Name).Find(&originalSequences); err != nil { + log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) + return err + } + sess.Engine().SetSchema(schema) + + for _, sequence := range originalSequences { + sequenceData := sequenceData{} + if _, err := sess.Table(sequence).Cols("last_value", "is_called").Get(&sequenceData); err != nil { + log.Error("Unable to get last_value and is_called from %s. Error: %v", sequence, err) + return err + } + sequenceMap[sequence] = sequenceData + + } + // CASCADE causes postgres to drop all the constraints on the old table if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s` CASCADE", tableName)); err != nil { log.Error("Unable to drop old table %s. Error: %v", tableName, err) @@ -578,7 +605,6 @@ func recreateTable(sess *xorm.Session, bean interface{}) error { } var indices []string - schema := sess.Engine().Dialect().URI().Schema sess.Engine().SetSchema("") if err := sess.Table("pg_indexes").Cols("indexname").Where("tablename = ? ", tableName).Find(&indices); err != nil { log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) @@ -594,6 +620,43 @@ func recreateTable(sess *xorm.Session, bean interface{}) error { } } + var sequences []string + sess.Engine().SetSchema("") + if err := sess.Table("information_schema.sequences").Cols("sequence_name").Where("sequence_name LIKE 'tmp_recreate__' || ? || '_%' AND sequence_catalog = ?", tableName, setting.Database.Name).Find(&sequences); err != nil { + log.Error("Unable to rename %s to %s. Error: %v", tempTableName, tableName, err) + return err + } + sess.Engine().SetSchema(schema) + + for _, sequence := range sequences { + newSequenceName := strings.Replace(sequence, "tmp_recreate__", "", 1) + if _, err := sess.Exec(fmt.Sprintf("ALTER SEQUENCE `%s` RENAME TO `%s`", sequence, newSequenceName)); err != nil { + log.Error("Unable to rename %s sequence to %s. Error: %v", sequence, newSequenceName, err) + return err + } + val, ok := sequenceMap[newSequenceName] + if newSequenceName == tableName+"_id_seq" { + if ok && val.LastValue != 0 { + if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', %d, %t)", newSequenceName, val.LastValue, val.IsCalled)); err != nil { + log.Error("Unable to reset %s to %d. Error: %v", newSequenceName, val, err) + return err + } + } else { + // We're going to try to guess this + if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', COALESCE((SELECT MAX(id)+1 FROM `%s`), 1), false)", newSequenceName, tableName)); err != nil { + log.Error("Unable to reset %s. Error: %v", newSequenceName, err) + return err + } + } + } else if ok { + if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', %d, %t)", newSequenceName, val.LastValue, val.IsCalled)); err != nil { + log.Error("Unable to reset %s to %d. Error: %v", newSequenceName, val, err) + return err + } + } + + } + case setting.Database.UseMSSQL: // MSSQL will drop all the constraints on the old table if _, err := sess.Exec(fmt.Sprintf("DROP TABLE `%s`", tableName)); err != nil { diff --git a/models/migrations/v175.go b/models/migrations/v175.go new file mode 100644 index 0000000000..0044ed1845 --- /dev/null +++ b/models/migrations/v175.go @@ -0,0 +1,53 @@ +// Copyright 2021 The Gitea Authors. All rights reserved. +// Use of this source code is governed by a MIT-style +// license that can be found in the LICENSE file. + +package migrations + +import ( + "fmt" + "regexp" + + "code.gitea.io/gitea/modules/log" + "code.gitea.io/gitea/modules/setting" + "xorm.io/xorm" +) + +func fixPostgresIDSequences(x *xorm.Engine) error { + if !setting.Database.UsePostgreSQL { + return nil + } + + sess := x.NewSession() + defer sess.Close() + if err := sess.Begin(); err != nil { + return err + } + + var sequences []string + schema := sess.Engine().Dialect().URI().Schema + + sess.Engine().SetSchema("") + if err := sess.Table("information_schema.sequences").Cols("sequence_name").Where("sequence_name LIKE 'tmp_recreate__%_id_seq%' AND sequence_catalog = ?", setting.Database.Name).Find(&sequences); err != nil { + log.Error("Unable to find sequences: %v", err) + return err + } + sess.Engine().SetSchema(schema) + + sequenceRegexp := regexp.MustCompile(`tmp_recreate__(\w+)_id_seq.*`) + + for _, sequence := range sequences { + tableName := sequenceRegexp.FindStringSubmatch(sequence)[1] + newSequenceName := tableName + "_id_seq" + if _, err := sess.Exec(fmt.Sprintf("ALTER SEQUENCE `%s` RENAME TO `%s`", sequence, newSequenceName)); err != nil { + log.Error("Unable to rename %s to %s. Error: %v", sequence, newSequenceName, err) + return err + } + if _, err := sess.Exec(fmt.Sprintf("SELECT setval('%s', COALESCE((SELECT MAX(id)+1 FROM `%s`), 1), false)", newSequenceName, tableName)); err != nil { + log.Error("Unable to reset sequence %s for %s. Error: %v", newSequenceName, tableName, err) + return err + } + } + + return sess.Commit() +} -- cgit v1.2.3