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

From: "Adam Rauch" <adam(at)labkey(dot)com>
To: <pgsql-testers(at)postgresql(dot)org>
Subject: [TEST REPORT] JDBC reports invalid primary key meta data after PK column rename
Date: 2010-05-04 17:39:40
Message-ID: 02cb01caebb0$c6b20f50$54162df0$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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();
}
}

Responses

Browse pgsql-testers by date

  From Date Subject
Next Message Josh Berkus 2010-05-04 22:30:20 Re: [TEST REPORT] JDBC reports invalid primary key meta data after PK column rename
Previous Message Josh Berkus 2010-05-03 02:00:33 Re: Trying to GRANT privileges on a newly-created database