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

>  
>  
>  From: David G. Johnston <david.g.johnston@gmail.com> 
>  Sent: Wednesday, September 8, 2021 15:53
>  To: ldh@laurent-hasson.com
>  Cc: pgsql-jdbc@lists.postgresql.org
>  Subject: Re: Duplicate tables information through metadata queries
>  
>  On Wednesday, September 8, 2021, mailto:ldh@laurent-hasson.com <mailto:ldh@laurent-hasson.com> wrote:
>  
>  The only difference I saw was in the “remarks” for the two tables, with one of them saying "btree comparison
function"which is strange.
 
>  
>  
>  This would seem to indicate that the catalog pg_description has two rows for this particular table and thus the join
toit [1] causes the single pg_class entry to become duplicated.
 
>  
>  Per the comment command page each object gets at most one comment so having multiples in the catalog is data
corruption. If you confirm that you do indeed have duplicates hopefully issuing create   comment on the problematic
recordsclears up the issue.
 
>  
>  David J.
>  
>
[1] https://github.com/pgjdbc/pgjdbc/blob/151b287732a551c380dcaa34f9c0549aeeb26208/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L1314
>  
> 


Hello David,

I think this is it!


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                |
 


I am not sure about your solution for cleanup however. Do you mean?


COMMENT ON TABLE PEOPLE.Contact IS 'Blah';


This doesn't seem to have any effect except updating the comment for the second row above. Should I just delete the
recordfrom the table for the tables affected? I know this is generally not good practice AT ALL... 😊
 

Also, you mention data corruption, but would that survive a backup/restore?


Thank you,
Laurent.