Duplicate tables information through metadata queries - Mailing list pgsql-jdbc

Hello,

I have recently found some strange behavior when getting a list of tables using the JDBC meta-data APIs: I am getting duplicate results for some tables. I have reproduced this on Postgres 11.2, 13.4, with drivers 42.2.15 and 42.2.19, Java 15 JVM all running locally on a Windows 10 laptop, and also tried on a Windows Server 2012 VM. I am doing the following:

import java.sql.*;

public class MetaDataTest

  {

    public static void main(String[] args)

    throws Exception

      {

        Class.forName("org.postgresql.Driver");

        java.sql.Connection C = DriverManager.getConnection("jdbc:postgresql://localhost:5432/Pepper", "postgres", args[0]);

        DatabaseMetaData meta = C.getMetaData();

        ResultSet RS1 = meta.getTables(null, "people", "contact", null);

        while (RS1.next() != false)

          {

            System.out.println("Table PEOPLE.Contact");

            printResult(RS1, "   ");

            ResultSet RS2 = meta.getColumns(null, "people", "contact", null);

            System.out.println("   Columns:");

            while (RS2.next() != false)

              printResult(RS2, "      ");

          }

      }

    protected static void printResult(ResultSet RS, String header)

    throws SQLException

      {

        StringBuilder str = new StringBuilder(header);

        int count = RS.getMetaData().getColumnCount();

        for (int i = 1; i <= count; ++i)

         str.append(RS.getMetaData().getColumnName(i) + ":" + RS.getString(i) + "; ");

        System.out.println(str.toString());

      }

  }

What’s weird is that I am getting 2 results for that one table I am looking for. This happens for a handful of tables across our environment consisting of 24 schemas and over 500 tables. This is a database that has existed on 11 and was migrated to 13. For example, for one of those tables, this is what I am getting from the code above:

Table PEOPLE.Contact

   table_cat:null; table_schem:people; table_name:contact; table_type:TABLE; remarks:btree comparison function; type_cat:; type_schem:; type_name:; self_referencing_col_name:; ref_generation:;

   Columns:

      TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:person_rn; DATA_TYPE:-5; TYPE_NAME:int8; COLUMN_SIZE:19; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The person this contact record belongs to; COLUMN_DEF:null; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:19; ORDINAL_POSITION:1; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;

      TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:type; DATA_TYPE:1; TYPE_NAME:bpchar; COLUMN_SIZE:5; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The type of this contact; COLUMN_DEF:'HM'::bpchar; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:5; ORDINAL_POSITION:2; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;

      ...

  table_cat:null; table_schem:people; table_name:contact; table_type:TABLE; remarks:blah blah; type_cat:; type_schem:; type_name:; self_referencing_col_name:; ref_generation:;

   Columns:

      TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:person_rn; DATA_TYPE:-5; TYPE_NAME:int8; COLUMN_SIZE:19; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The person this contact record belongs to; COLUMN_DEF:null; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:19; ORDINAL_POSITION:1; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;

      TABLE_CAT:null; TABLE_SCHEM:people; TABLE_NAME:contact; COLUMN_NAME:type; DATA_TYPE:1; TYPE_NAME:bpchar; COLUMN_SIZE:5; BUFFER_LENGTH:null; DECIMAL_DIGITS:0; NUM_PREC_RADIX:10; NULLABLE:0; REMARKS:The type of this contact; COLUMN_DEF:'HM'::bpchar; SQL_DATA_TYPE:null; SQL_DATETIME_SUB:null; CHAR_OCTET_LENGTH:5; ORDINAL_POSITION:2; IS_NULLABLE:NO; SCOPE_CATALOG:null; SCOPE_SCHEMA:null; SCOPE_TABLE:null; SOURCE_DATA_TYPE:null; IS_AUTOINCREMENT:NO; IS_GENERATEDCOLUMN:;

      ...

The only difference I saw was in the “remarks” for the two tables, with one of them saying "btree comparison function" which is strange.

If I query the Postgres information schema, I am only seeing 1 result:

select * from information_schema.tables WHERE table_name='contact'

table_catalog|table_schema|table_name|table_type|self_referencing_column_name|reference_generation|user_defined_type_catalog|user_defined_type_schema|user_defined_type_name|is_insertable_into|is_typed|commit_action|

-------------+------------+----------+----------+----------------------------+--------------------+-------------------------+------------------------+----------------------+------------------+--------+-------------+

Pepper       |people      |contact   |BASE TABLE|[NULL]                      |[NULL]              |[NULL]                   |[NULL]                  |[NULL]                |YES               |NO      |[NULL]       |

Same if I query the PG Catalog

SELECT *

FROM pg_catalog.pg_class c

     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relname = 'contact'

oid  |relname|relnamespace|reltype|reloftype|relowner|relam|relfilenode|reltablespace|relpages|reltuples|relallvisible|reltoastrelid|relhasindex|relisshared|relpersistence|relkind|relnatts|relchecks|relhasrules|relhastriggers|relhassubclass|relrowsecurity|relforcerowsecurity|relispopulated|relreplident|relispartition|relrewrite|relfrozenxid|relminmxid|relacl                                                                                                    |reloptions|relpartbound|oid  |nspname|nspowner|nspacl                                                                 |

-----+-------+------------+-------+---------+--------+-----+-----------+-------------+--------+---------+-------------+-------------+-----------+-----------+--------------+-------+--------+---------+-----------+--------------+--------------+--------------+-------------------+--------------+------------+--------------+----------+------------+----------+----------------------------------------------------------------------------------------------------------+----------+------------+-----+-------+--------+-----------------------------------------------------------------------+

17181|contact|       16743|  17183|        0|      10|    2|      17181|            0|       1|     26.0|            1|        17187|true       |false      |p             |r      |      21|        0|false      |true          |false         |false         |false              |true          |d           |false         |         0|1675        |1         |{postgres=arwdDxt/postgres,tildareadonly=r/postgres,tilda_app=arwdDxt/postgres,tilda_read_only=r/postgres}|NULL      |[NULL]      |16743|people |      10|{postgres=UC/postgres,tilda_app=UC/postgres,tilda_read_only=U/postgres}|

I understand this is a difficult scenario to replicate although I do have 2 copies of that database in two different environments as per the above and the issue exists in both places: looks like this issue survives a backup/restore. I am not sure if I am doing something wrong in my Java code, or if I found a bug in the JDBC Driver…

Thank you,

Laurent.