Renaming primary keys is not persistent

[GoogleCodeExporter]

For example, I want to rename PK index to meaningful name:
 ALTER INDEX PRIMARY_KEY_3 RENAME TO IDX_PK_BANKS;
All works fine if I do it from H2 web-browser console.

But, DON'T WORK (index not renamed) if I call this, from JAVA CODE:
Connection jdbcConnection = DriverManager.getConnection("jdbc:h2:file:C:/
test/database/myDb", "sa", "");
...
Statement stmt = jdbcConnection.createStatement();
stmt.execute("ALTER INDEX PRIMARY_KEY_3 RENAME TO IDX_PK_BANKS;");
jdbcConnection.commit();
stmt.close();
...
jdbcConnection.close();

For other indexes (UNIQUE or REFERENTIAL) all work just fine both from 
console and java code.

It's look like a bug.

----------------------
Actually I want to have meaningful names for PK indexes, so I'm executing 
this select for auto-generating rename script:

SELECT 'ALTER INDEX ' || UNIQUE_INDEX_NAME  || ' RENAME TO IDX_' || 
CONSTRAINT_NAME || ';' AS script
 FROM INFORMATION_SCHEMA.CONSTRAINTS 
 WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY TABLE_NAME;

And then I iterate through ResultSet and execute each ALTER INDEX RENAME 
as described above.

Original issue reported on code.google.com by kua...@gmail.com on 1 Oct 2009 at 6:08

[GoogleCodeExporter]

I can't reproduce this problem. Do you do something like this in your 
application?

ResultSet rs = stat.executeQuery(...)
while (rs.next()) {
  ...
  stat.execute(...);
  ...
}

If this is the case, the problem is that the result set is closed when you use 
the
statement within the loop. You need to use a second statement. (this is not a 
bug, it
is required unfortunately by the JDBC API).

If this is not the problem, could you create a complete test case?

Original comment by thomas.t...@gmail.com on 2 Oct 2009 at 1:32

• Changed state: WorksForMe

[GoogleCodeExporter]

I found the problem. For renaming Indexes that are primary keys new name lost 
after 
disconnect. With H2 1.1.119 this test fails after reconnect (see comments in 
code).

here Test case for JUnit-4:

public class TestRenamePK
{
 private void executeStmt(Connection jdbcConnection, String sql) throws SQLException
 {
  Statement stmt = jdbcConnection.createStatement();
  try
  {
   stmt.execute(sql);
   jdbcConnection.commit();
  }
  finally
  {
   stmt.close();
  }
 }

 private String selectF1(Connection jdbcConnection, String sql) throws SQLException
 {
  Statement stmt = jdbcConnection.createStatement();
  try
  {
   ResultSet rs = stmt.executeQuery(sql);
   try
   {
    if (rs.next())
    {
     return (rs.getString(1));
    }

    return ("nothing");
   }
   finally
   {
    rs.close();
   }
  }
  finally
  {
   stmt.close();
  }
 }

 @Test
 public void testRenamePk()
 {
  try
  {
   Class.forName("org.h2.Driver");
   Connection jdbcConnection = DriverManager.getConnection("jdbc:h2:file:c:/Temp/
testRenamePK", "sa", "");
   try
   {
    this.executeStmt(jdbcConnection, "CREATE TABLE TEST(ID INTEGER NOT NULL);");
    Assert.assertEquals("TEST", this.selectF1(jdbcConnection,
     "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TEST';"));

    this.executeStmt(jdbcConnection, "ALTER TABLE PUBLIC.TEST ADD CONSTRAINT 
PUBLIC.PK_TEST PRIMARY KEY(ID);");
    Assert.assertEquals("1", this.selectF1(jdbcConnection, "SELECT COUNT(*) FROM 
INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'TEST' AND CONSTRAINT_TYPE = 
'PRIMARY KEY'"));

    String oldName = this.selectF1(jdbcConnection, "SELECT UNIQUE_INDEX_NAME FROM 
INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'TEST' AND CONSTRAINT_TYPE = 
'PRIMARY KEY'");
    String newName = "IDX_PK_TEST";

    this.executeStmt(jdbcConnection, "ALTER INDEX " + oldName + " RENAME TO " + 
newName + ";");
    // IN SAME session ALL - OK
    Assert.assertEquals(newName, this.selectF1(jdbcConnection, "SELECT 
UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'TEST' 
AND 
CONSTRAINT_TYPE = 'PRIMARY KEY'"));

    // HERE IS THE BUG - after reconnect TEST FAILS!
    jdbcConnection.close();
    jdbcConnection = DriverManager.getConnection("jdbc:h2:file:c:/Temp/
testRenamePK", "sa", "");
    Assert.assertEquals("Rename for PK index was lost:", newName, 
this.selectF1(jdbcConnection, "SELECT UNIQUE_INDEX_NAME FROM 
INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'TEST' AND CONSTRAINT_TYPE = 
'PRIMARY KEY'"));
   }
   finally
   {
    jdbcConnection.close();
   }
  }
  catch(Exception e)
  {
   Assert.assertTrue("Test failed: " + e.getMessage(), false);
  }
 }
}

Original comment by kua...@gmail.com on 2 Oct 2009 at 3:36

[GoogleCodeExporter]

Thanks a lot for the test case! Now I understand the problem. Currently the 
name of
the primary key is actually never persisted. I will fix that for the next 
release and
add a test case.

Original comment by thomas.t...@gmail.com on 2 Oct 2009 at 8:38

• Changed title: Renaming primary keys is not persistent
• Changed state: Accepted

[GoogleCodeExporter]

Fixed in version 1.2.120.

Original comment by thomas.t...@gmail.com on 4 Oct 2009 at 2:35

• Changed state: Fixed