pg.ddx.io  pgsql-testers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adam Rauch <adam@labkey.com>
To: pgsql-testers@postgresql.org
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> (raw)

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





view thread (3+ messages)  latest in thread

Message-ID: <02cb01caebb0$c6b20f50$54162df0$@com>
Permalink:  ../02cb01caebb0$c6b20f50$54162df0$@com/
Also on:    postgresql.org/message-id/02cb01caebb0$c6b20f50$54162df0$@com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-testers@postgresql.org
  Cc: adam@labkey.com
  Subject: Re: [TEST REPORT] JDBC reports invalid primary key meta data after PK column rename
  In-Reply-To: <02cb01caebb0$c6b20f50$54162df0$@com>
  References: <02cb01caebb0$c6b20f50$54162df0$@com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox