postgres: fix PK drop, don't assume PK name as TABLE_pkey
When dropping a primary key, postgres requires the primary key name to
be specified. In the UpdatePrimaryKeys init step it was assumed that
the primary key name for a table X is always X_pkey. However, since the
Schema_82 this pattern doesn't hold for those tables which were renamed
in that migration, as the migration only renamed the tables. For
example, before Schema_82 we had a table name:
account_group_includes_by_uuid_audit
and its primary key named:
account_group_includes_by_uuid_audit_pkey
This table was renamed to:
account_group_by_id_aud
but the primary key name didn't change. Therefore, the UpdatePrimaryKeys
failed to drop the primary key using the TABLE_pkey pattern and couldn't
then create the new primary key.
Instead of assuming the TABLE_pkey pattern get the old primary key
name from the DatabaseMetaData and use this name for dropping the
existing primary key.
Change-Id: I671b9912110fc22c842d2c15930a2c7b9906bd48
diff --git a/gerrit-pgm/src/main/java/com/google/gerrit/pgm/init/UpdatePrimaryKeys.java b/gerrit-pgm/src/main/java/com/google/gerrit/pgm/init/UpdatePrimaryKeys.java
index 0c99fcf..79bd730 100644
--- a/gerrit-pgm/src/main/java/com/google/gerrit/pgm/init/UpdatePrimaryKeys.java
+++ b/gerrit-pgm/src/main/java/com/google/gerrit/pgm/init/UpdatePrimaryKeys.java
@@ -42,6 +42,11 @@
public class UpdatePrimaryKeys implements InitStep {
+ private static class PrimaryKey {
+ String oldNameInDb;
+ List<String> cols;
+ }
+
private final ConsoleUI ui;
private SchemaFactory<ReviewDb> dbFactory;
@@ -64,7 +69,7 @@
try {
conn = ((JdbcSchema) db).getConnection();
dialect = ((JdbcSchema) db).getDialect();
- Map<String, List<String>> corrections = findPKUpdates();
+ Map<String, PrimaryKey> corrections = findPKUpdates();
if (corrections.isEmpty()) {
return;
}
@@ -76,7 +81,7 @@
ui.message("fixing primary keys...\n");
JdbcExecutor executor = new JdbcExecutor(conn);
try {
- for (Map.Entry<String, List<String>> c : corrections.entrySet()) {
+ for (Map.Entry<String, PrimaryKey> c : corrections.entrySet()) {
ui.message(" table: %s ... ", c.getKey());
recreatePK(executor, c.getKey(), c.getValue());
ui.message("done\n");
@@ -96,19 +101,20 @@
this.dbFactory = dbFactory;
}
- private Map<String, List<String>> findPKUpdates()
+ private Map<String, PrimaryKey> findPKUpdates()
throws OrmException, SQLException {
- Map<String, List<String>> corrections = new TreeMap<>();
+ Map<String, PrimaryKey> corrections = new TreeMap<>();
ReviewDb db = dbFactory.open();
try {
DatabaseMetaData meta = conn.getMetaData();
JavaSchemaModel jsm = new JavaSchemaModel(ReviewDb.class);
for (RelationModel rm : jsm.getRelations()) {
String tableName = rm.getRelationName();
- List<String> expectedPK = relationPK(rm);
- List<String> actualPK = dbTablePK(meta, tableName);
- if (!expectedPK.equals(actualPK)) {
- corrections.put(tableName, expectedPK);
+ List<String> expectedPKCols = relationPK(rm);
+ PrimaryKey actualPK = dbTablePK(meta, tableName);
+ if (!expectedPKCols.equals(actualPK.cols)) {
+ actualPK.cols = expectedPKCols;
+ corrections.put(tableName, actualPK);
}
}
return corrections;
@@ -126,7 +132,7 @@
return pk;
}
- private List<String> dbTablePK(DatabaseMetaData meta, String tableName)
+ private PrimaryKey dbTablePK(DatabaseMetaData meta, String tableName)
throws SQLException {
if (meta.storesUpperCaseIdentifiers()) {
tableName = tableName.toUpperCase();
@@ -136,14 +142,18 @@
ResultSet cols = meta.getPrimaryKeys(null, null, tableName);
try {
+ PrimaryKey pk = new PrimaryKey();
Map<Short, String> seqToName = new TreeMap<>();
while (cols.next()) {
seqToName.put(cols.getShort("KEY_SEQ"), cols.getString("COLUMN_NAME"));
+ if (pk.oldNameInDb == null) {
+ pk.oldNameInDb = cols.getString("PK_NAME");
+ }
}
- List<String> pk = new ArrayList<>(seqToName.size());
+ pk.cols = new ArrayList<>(seqToName.size());
for (String name : seqToName.values()) {
- pk.add(name.toLowerCase(Locale.US));
+ pk.cols.add(name.toLowerCase(Locale.US));
}
return pk;
} finally {
@@ -152,20 +162,19 @@
}
private void recreatePK(StatementExecutor executor, String tableName,
- List<String> cols) throws OrmException {
- try {
+ PrimaryKey pk) throws OrmException {
+ if (pk.oldNameInDb == null) {
+ ui.message("WARN: primary key for table %s didn't exist ... ", tableName);
+ } else {
if (dialect instanceof DialectPostgreSQL) {
// postgresql doesn't support the ALTER TABLE foo DROP PRIMARY KEY form
executor.execute("ALTER TABLE " + tableName + " DROP CONSTRAINT "
- + tableName + "_pkey");
+ + pk.oldNameInDb);
} else {
executor.execute("ALTER TABLE " + tableName + " DROP PRIMARY KEY");
}
- } catch (OrmException ignore) {
- // maybe the primary key was dropped in a previous run but the creation failed
- ui.message("WARN: %s\n", ignore.getMessage());
}
executor.execute("ALTER TABLE " + tableName
- + " ADD PRIMARY KEY(" + Joiner.on(",").join(cols) + ")");
+ + " ADD PRIMARY KEY(" + Joiner.on(",").join(pk.cols) + ")");
}
}
