[TEST REPORT] JDBC reports invalid primary key meta data after PK column rename

[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();
}
}

Adam,

This is an intentional change in behavior. I've contacted the JDBC
driver authors about being ready for it in the new release of pgJDBC for
9.0. I'll contact other driver authors as well.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Just so I'm clear, does this mean that 9.0 provides a way for the JDBC driver authors to get the correct PK meta data so they can fix this problem in the 9.0 JDBC driver?

If so, it implies that the current and previous JDBC drivers will break with 9.0, which seems unfortunate... but I suppose it's better than not being able to work with 9.0 at all.

Thanks,
Adam

-----Original Message-----
From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
Sent: Tuesday, May 04, 2010 3:30 PM
To: Adam Rauch
Cc: pgsql-testers(at)postgresql(dot)org
Subject: Re: [TESTERS] [TEST REPORT] JDBC reports invalid primary key meta data after PK column rename

Adam,

This is an intentional change in behavior. I've contacted the JDBC
driver authors about being ready for it in the new release of pgJDBC for
9.0. I'll contact other driver authors as well.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com