Subject: [pgjdbc/pgjdbc] issue #3467: DatabaseMetaData.getIndexInfo() doesn't return "REMARKS" property

From: "ldhasson (@ldhasson)" <[email-masked]>
Date: Sun, 22 Dec 2024 17:32:20 +0000

Please read https://stackoverflow.com/help/minimal-reproducible-example 

**Describe the issue**
Querying the meta-data for indices doesn't return the comment. A property called "REMARKS" is expected as for tables, views and columns.

**Driver Version?** 
postgresql-42.7.4.jar

**Java Version?**
21

**OS Version?**
Windows 11

**PostgreSQL Version?**
PostgreSQL 15.1
PostgreSQL 17.1

**To Reproduce**
Database:
```sql
CREATE TABLE Test_XXX(id BIGINT);
CREATE UNIQUE INDEX IF NOT EXISTS Test_XXX_main ON Test_XXX("id" ASC) where id > 0;
COMMENT ON INDEX Test_XXX_main IS E'Blah blah blah';
```

Java:
```java
        DatabaseMetaData meta = C.getMetaData();
        ResultSet RS = meta.getIndexInfo(null, null, "test_xxx", false, true);
        while (RS.next() == true)
          {
            int count = RS.getMetaData().getColumnCount();            
            for (int i = 1; i <= count; ++i)
              {
                String Name = RS.getMetaData().getColumnName(i);
                String Val = RS.getString(i);
                System.out.println("   - "+Name+": "+Val);
              }
          }
```

Output:
```log
   - TABLE_CAT: null
   - TABLE_SCHEM: public
   - TABLE_NAME: test_xxx
   - NON_UNIQUE: t
   - INDEX_QUALIFIER: null
   - INDEX_NAME: test_xxx_main
   - TYPE: 3
   - ORDINAL_POSITION: 1
   - COLUMN_NAME: id
   - ASC_OR_DESC: A
   - CARDINALITY: 0
   - PAGES: 1
   - FILTER_CONDITION: (id > 0)
```

**Expected behaviour**
The meta-data should return a property called "REMARKS" containing the comment on the index.

---

From: "Shanayshah44 (@Shanayshah44)" <[email-masked]>
Date: Mon, 30 Dec 2024 03:57:16 +0000

The "REMARKS" field does not exist as a direct part of the metadata but can be retrieved from the pg_class and pg_description tables.But if you use the other than the Postgres like Oracle or any other it is directly available in the metadata.

Here how you can fetch the "REMARKS".

"SELECT pg_class.relname AS index_name, 
        pg_description.description AS comment 
        FROM pg_class 
        LEFT JOIN pg_description ON pg_description.objoid = pg_class.oid 
        JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace 
        WHERE pg_class.relname = test_xxx_main AND pg_namespace.nspname = 'public'";

---

From: "ldhasson (@ldhasson)" <[email-masked]>
Date: Mon, 30 Dec 2024 04:09:53 +0000

Hello! Thank you!

I know about the alternative query, but that becomes PG-only. It's also a second query to run. Isn't this something that just should be supported at the JDBC level?

Thank you,
Laurent Hasson

________________________________
From: Shanay Shah ***@***.***>
Sent: Sunday, December 29, 2024 22:57
To: pgjdbc/pgjdbc
Cc: ***@***.***; Author
Subject: Re: [pgjdbc/pgjdbc] DatabaseMetaData.getIndexInfo() doesn't return "REMARKS" property (Issue #3467)

—
Reply to this email directly, view it on GitHub<https://github.com/pgjdbc/pgjdbc/issues/3467#issuecomment-2565000122>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AADEHBZQFT3JBMWIWD3XTJT2IDADDAVCNFSM6AAAAABUB2ZFOGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNRVGAYDAMJSGI>.
You are receiving this because you authored the thread.Message ID: ***@***.***>

---

From: "Shanayshah44 (@Shanayshah44)" <[email-masked]>
Date: Mon, 30 Dec 2024 04:12:31 +0000

Yes of course this should be supported at the JDBC level

---

From: "ldhasson (@ldhasson)" <[email-masked]>
Date: Mon, 30 Dec 2024 05:12:22 +0000

Ok. Your previous message seemed to imply this would not be part of the JDBC metadata and so one should use the alternate query.

It would be nice to have this covered in an upcoming release. Seems like it would be a low-impact addition/fix.

Thank you,
Laurent Hasson

________________________________
From: Shanay Shah ***@***.***>
Sent: Sunday, December 29, 2024 23:12
To: pgjdbc/pgjdbc
Cc: ***@***.***; Author
Subject: Re: [pgjdbc/pgjdbc] DatabaseMetaData.getIndexInfo() doesn't return "REMARKS" property (Issue #3467)

—
Reply to this email directly, view it on GitHub<https://github.com/pgjdbc/pgjdbc/issues/3467#issuecomment-2565006997>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AADEHB25V2RIN27UHZQNFOD2IDB4LAVCNFSM6AAAAABUB2ZFOGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNRVGAYDMOJZG4>.
You are receiving this because you authored the thread.Message ID: ***@***.***>

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 30 Dec 2024 10:23:35 +0000

@ldhasson Here https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo-java.lang.String-java.lang.String-java.lang.String-boolean-boolean- is the javadoc for getIndexInfo. I do not see a `REMARKS` field.

---

From: "ldhasson (@ldhasson)" <[email-masked]>
Date: Mon, 30 Dec 2024 14:21:04 +0000

This is interesting. I thought i remember Oracle having that property. I don't have an Oracle database in hand now. But i see from the 4.3 standard that REMARKS is available in most places, but not for indices.

Is this an "enhancement" the team might consider? Or are you sticking strictly to the standard? In 2024, it still feels like JDBC is missing really basic capabilities that most databases support 🙁

Thank you.
Laurent.

________________________________
From: Dave Cramer ***@***.***>
Sent: Monday, December 30, 2024 5:23 AM
To: pgjdbc/pgjdbc ***@***.***>
Cc: ***@***.*** ***@***.***>; Mention ***@***.***>
Subject: Re: [pgjdbc/pgjdbc] DatabaseMetaData.getIndexInfo() doesn't return "REMARKS" property (Issue #3467)

@ldhasson<https://github.com/ldhasson> Here https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo-java.lang.String-java.lang.String-java.lang.String-boolean-boolean- is the javadoc for getIndexInfo. I do not see a REMARKS field.

—
Reply to this email directly, view it on GitHub<https://github.com/pgjdbc/pgjdbc/issues/3467#issuecomment-2565289064>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AADEHB6VVZ3ABXO5UFXA4KT2IENL3AVCNFSM6AAAAABUB2ZFOGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNRVGI4DSMBWGQ>.
You are receiving this because you were mentioned.Message ID: ***@***.***>

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 30 Dec 2024 14:36:38 +0000

We generally stick to the standard, but I see no reason not to include it. PR's are welcome

---

From: "raminorujov (@raminorujov)" <[email-masked]>
Date: Thu, 06 Feb 2025 12:20:23 +0000

> We generally stick to the standard, but I see no reason not to include it. PR's are welcome

Hi, @davecramer is it worth to merge my PR(https://github.com/pgjdbc/pgjdbc/pull/3513) or should I close it?

---

From: "raminorujov (@raminorujov)" <[email-masked]>
Date: Thu, 06 Feb 2025 14:04:50 +0000

@ldhasson can you close this issue? I fixed it in my PR.

---

From: "ldhasson (@ldhasson)" <[email-masked]>
Date: Thu, 06 Feb 2025 14:11:25 +0000

Wonderful to hear. Thanks team!!! Looking forward to playing with it.