[TEST REPORT] JDBC reports invalid primary key meta data after PK column rename - Mailing list pgsql-testers

[TEST REPORT]
[Release]: 9.0Beta1
[Test Type]: Application
[Test]: Retrieve primary key meta data via JDBC
[conn.getMetaData().getPrimaryKeys()] from table whose PK column has been
renamed
[Platform]: Windows 7 64-Bit
[Parameters]: Using JDBC4-701 driver
[Failure]: Yes, compatibility issue (fails on 9.0, works fine on 8.x)
[Results]: After rename, JDBC primary key meta data claims a non-existent
column is part of the primary key
[Comments]:

Execute this SQL:

   CREATE TABLE public.Customers (RowId INT PRIMARY KEY);
   ALTER TABLE public.Customers RENAME RowId TO CustomerId;

After renaming the primary key column, the information_schema reports the
correct column name (CustomerId):

   SELECT * FROM information_schema.constraint_column_usage WHERE
table_schema = 'public' and table_name = 'customers';

However, the standard JDBC method DatabaseMetaData.getPrimaryKeys(catalog,
schema, table) still reports the non-existent column "RowId" as the primary
key column.  A small Java application that demonstrates the problem is
attached below.  Assuming 8.x is running on a different port, changing the
port number shows that 8.x reports the correct primary key meta data
information.

This is a major problem for our application, which requires correct primary
key meta data to function, and has no control over or knowledge of the DDL
used to produce the PostgreSQL tables on which it operates.  We rely
exclusively on accurate meta data being reported by the database.

Thanks,
Adam



import java.sql.*;

public class Main
{
    // Java code that demonstrates invalid primary key meta data reported
after column rename in PostgreSQL 9.0 Beta 1
    //
    // Assumptions:
    //
    // 1. PostgreSQL JDBC driver on the Java classpath
    // 2. PostgreSQL 9.0 Beta 1 running on localhost:5433 with credentials
as specified
    // 3. Existing database called "labkey"
    //
    public static void main(String[] args) throws ClassNotFoundException,
SQLException
    {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:5433/labkey";
        Connection conn = DriverManager.getConnection(url, "postgres",
"sasa");

        // Create simple Customers table with RowId as PK
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE public.Customers (RowId INT PRIMARY
KEY)");
        stmt.close();
        logPkMetaData(conn, "labkey", "public", "customers");

        // Rename the PK column to CustomerId
        stmt = conn.createStatement();
        stmt.execute("ALTER TABLE public.Customers RENAME RowId TO
CustomerId");
        stmt.close();

        // Note that primary key meta data still reports non-existent row
"RowId" as part of primary key
        logPkMetaData(conn, "labkey", "public", "customers");

        // Clean up
        stmt = conn.createStatement();
        stmt.execute("DROP TABLE public.Customers");
        stmt.close();

        conn.close();
    }

    private static void logPkMetaData(Connection conn, String catalog,
String schema, String table) throws SQLException
    {
        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet rs = dbmd.getPrimaryKeys(catalog, schema, table);
        ResultSetMetaData rsmd = rs.getMetaData();

        for (int i = 1; i <= rsmd.getColumnCount(); i++)
            System.out.print(rsmd.getColumnName(i) + "\t");

        System.out.println();

        while (rs.next())
        {
            for (int i = 1; i <= rsmd.getColumnCount(); i++)
                System.out.print(rs.getString(i) + "\t");

            System.out.println();
        }

        System.out.println();
        rs.close();
    }
}