Обсуждение: Duplicate comment on a table

Hello,

I am using JDBC to get the details about tables and seeing duplicates coming back for a handful of tables. My schema has over 300 tables. The code is very straightforward and as follows:

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)

          printResult(RS1, "   ");

      }

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

      }

  }

I am getting two records:

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:;

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:;

Notice how everything matches except for “remarks”. I posted a question on the JDBC mailing list and the issue was identified has having to do with the catalog tables. The query issued by the driver is similar to the following:

SELECT *

FROM pg_catalog.pg_class c

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

     LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)

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                                                                 |objoid|classoid|objsubid|description              |

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

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}| 17181|    1255|       0|btree comparison function|

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}| 17181|    1259|       0|blah blah                |

So, there are TWO records in the table pg_catalog.pg_description for a given table. I have no idea how this might have occurred and I know it “survives” a backup/restore. I am not sure how to fix this. It was suggested I vacuum full the table and reset the comment, but that didn’t work:

VACUUM FULL FREEZE ANALYZE  PEOPLE.contact;

COMMENT ON TABLE PEOPLE.Contact IS 'Blah';

I am refraining of course from simply deleting the offending row in pg_catalog.pg_description because I know this is terrible practice in general 😊 So unsure how I can fix this.

Thank you,

Laurent.