Running the EnterpriseDB windows package for PostgreSQL 8.3. The installer fails when attempting to upgrade from OpenNMS 1.8.0 to 1.8.1 (but also appears to happen with upgrading from older upgrades to 1.7+).

The problem appears to be the same as that identified by Daniel Greske in June on the -install mailing list:

When I run the installer ($OPENNMS_HOME/bin/install -l /usr/local/lib
-dis) to complete the upgrade, everything goes well until this line:
[...]

• using 'opennms' as the PostgreSQL database name for OpenNMS

• checking if database "opennms" is unicode... ALREADY UNICODE

• Checking for old import files in /opt/opennms/etc... DONE
  Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
  syntax error at or near "$1"
  **Position: 13

  • * * *at
    org.postgresql.core.v3.QueryExecutorImpl.receiveEr rorResponse(QueryExecutorImpl.java:2062)

  • * * *at
    org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1795)

  • * * *at
    org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:257)

  • * * *at
    org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:479)

  • * * *at
    org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:367)

  • * * *at
    org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:360)

  • * * *at
    org.opennms.netmgt.dao.db.InstallerDb.databaseSetU ser(InstallerDb.java:1940)

  • * * *at
    org.opennms.install.Installer.install(Installer.ja va:278)

  • * * *at
    org.opennms.install.Installer.main(Installer.java: 859)

I did a little more digging and it looks like the PostgreSQL driver on Windows is sometimes returning tables, indices, and sequences during the statement

ResultSet rs = getAdminConnection().getMetaData().getTables(null, "public", "%", null);

The subsequent ALTER TABLE statement then fails on the first non-table object encountered in the HashSet built from that ResultSet.

One way to ensure that only tables are being acted upon would be to add some filtering to the databaseSetUser() method. Something like the following might work as a workaround for the PostgreSQL behaviour without affecting the results other drivers.

public void databaseSetUser() throws SQLException {
ResultSet rs = getAdminConnection().getMetaData().getTables(null, "public", "%", null);
HashSet<String> objects = new HashSet<String>();
while (rs.next()) {
if (rs.getString("TABLE_TYPE").equals("TABLE"))
objects.add(rs.getString("TABLE_NAME"));
}
PreparedStatement st = getAdminConnection().prepareStatement("ALTER TABLE ? OWNER TO ?");
for (String objName : objects) {
st.setString(1, objName);
st.setString(2, m_user);
st.execute();
}
st.close();
}

I am currently running into this on Windows with the PG 8.4-701 JDBC3 driver. I have had success doing a db upgrade to 1.8 under CentOS 5 with the same PG 8.4-701 driver against a Linux RPM Postgres db, so maybe it's a difference between the Windows and Linux PostgreSQL implementations.