Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 1B9CB632A6C for ; Tue, 4 May 2010 14:49:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 43984-04-4 for ; Tue, 4 May 2010 17:49:23 +0000 (UTC) X-Greylist: delayed 00:09:33.519295 by SQLgrey-1.7.6 Received: from out1.smtp.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by mail.postgresql.org (Postfix) with ESMTP id 8A108633E3C for ; Tue, 4 May 2010 14:49:23 -0300 (ADT) Received: from compute1.internal (compute1.internal [10.202.2.41]) by gateway1.messagingengine.com (Postfix) with ESMTP id 3FCE6F1CDC for ; Tue, 4 May 2010 13:39:40 -0400 (EDT) Received: from heartbeat2.messagingengine.com ([10.202.2.161]) by compute1.internal (MEProxy); Tue, 04 May 2010 13:39:40 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=messagingengine.com; h=from:to:subject:date:message-id:mime-version:content-type:content-transfer-encoding; s=smtpout; bh=79XvCTLb8US1FoazUvFeTHmgx2U=; b=Cr9JwGMI4q487gnuV7830RkjTyeZXJctWSmVOUoGlIo4PekV5JqLJx6xLJ86hqQCsqbGA2NyvPxqaFqAWYSEMfHlgps0eiPhvnit+s5galCmZTwBfG7AZeKNM1fGHH7086k5puw95nBWBmC5+C3QA9HAuc88/MR9R3OcJzrySvU= X-Sasl-enc: LmC98OKSg0rLpnFIsKVI6Esj9OcaixchQbCdBbBPJmL6 1272994779 Received: from AdamE6400 (DHCP155014.FHCRC.ORG [140.107.155.14]) by mail.messagingengine.com (Postfix) with ESMTPA id A9102513DC for ; Tue, 4 May 2010 13:39:39 -0400 (EDT) From: "Adam Rauch" To: Subject: [TEST REPORT] JDBC reports invalid primary key meta data after PK column rename Date: Tue, 4 May 2010 10:39:40 -0700 Message-ID: <02cb01caebb0$c6b20f50$54162df0$@com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Content-Language: en-us Thread-Index: AcrrsMXst368BL/DSxeVsWyk4PgxwQ== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.2 tagged_above=-10 required=5 tests=BAYES_05=-0.5, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201005/4 X-Sequence-Number: 87 [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(); } }