Subject: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Fri, 31 Jan 2025 09:14:32 +0000

I am using a simple JDBC code to connect to postgres database.
This is the jdbc url: 
**jdbc:postgresql://host:port/pg1**

Here, "pg1" is the database name.

Issue 1:
Expectation is that **connection.getMetaData().getCatalogs()** sends only pg1 in the response as that is the one passed in JDBC url but it fetches all the catalogs present in the DB.

Issue2

**connection.setCatalog("pg1");
connection.getMetaData().getSchemas()**  --> should fetch only the schemas present in catalog 'pg1' but it fetches schemas of other catalogs as well.

Please note that this issue is happening only in **postgresql-42.7.4.jar** whereas the previous version **postgresql-42.6.0.jar** works as expected.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 31 Jan 2025 13:23:25 +0000

Interesting, can you try with the latest 42.7.5 ?

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Mon, 03 Feb 2025 04:38:50 +0000

Well Same with latest version as well.

Below is the sample code
-------------------------------------------------------------------
```
public class DBFetchPostgresSchemas {
   static final String DB_URL = "jdbc:postgresql://host:5434/postgresutf8";
   static final String USER = "user";
   static final String PASS = "password";

   public static void main(String[] args) {
	
	   try {
                getCatalogs();
		getSchemas("postgres");
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
   }
   
   public static void getCatalogs() throws SQLException 
   {
       Connection conn = DriverManager.getConnection(DB_URL, USER,PASS);
       ResultSet rs = null;
       try
       {
           List<String> catalogs = new ArrayList<String>();
           rs = conn.getMetaData().getCatalogs();
           while (rs.next())
           {
              String catalog = rs.getString("TABLE_CAT");
              System.out.println("catalog:"+catalog);
           }
       }
       catch (SQLException e)
       {
           e.printStackTrace();
       }
       finally
       {
    	   rs.close();
           conn.close();
       }

   }

   public static void getSchemas(String catalog) throws SQLException 
   {
       Connection conn = DriverManager.getConnection(DB_URL, USER,PASS);;
       ResultSet rs = null;
       try
       {
           List<String> schemas = new ArrayList<String>();
         
           if (catalog != null && catalog.length() > 0)
               conn.setCatalog(catalog);
           rs = conn.getMetaData().getSchemas();
           
           while (rs.next())
           {
               String schema = rs.getString("TABLE_SCHEM");
               System.out.println("schema:"+schema);
              
           }
       }
       catch (SQLException e)
       {
          e.printStackTrace();
       }
       finally
       {
           rs.close();
           conn.close();
       }
   }

}
```

===========================

Here **getCatalog()** --> gives all the catalogs present in Database. 

Version **42.6** gives only the catalog passed in JDBC url where as **42.7**  gives all the catalogs from database which is still fine but the problem arises with **getSchema()** -> It always gives schema of the one passed in JDBCUrl rather than the one passed in request.

In above example, "**postgres**" catalog is passed in request but it fetches the schema of "**postgresutf8**" catalog which is passed in db URL

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 15:58:49 +0000

I just tried this with the latest 42.7.5 and was unable to replicate these results:
```
public static void getSchemas(String catalog) throws SQLException
        {
            try (Connection conn = DriverManager.getConnection(DB_URL, USER,PASS))
            {
                ResultSet rs = null;

                if (catalog != null && catalog.length() > 0){
                    System.out.println(String.format("Connected to %s, setting catalog: %s",DB_URL, catalog));
                    conn.setCatalog(catalog);
                }

                while (rs.next())
                {

                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
```
Produces  when connected to database `foo`

```
Connected to jdbc:postgresql://localhost:5432/foo, setting catalog: foo
schema:blah
schema:information_schema
schema:pg_catalog
schema:public
```
and when connected to database `test`
```
Connected to jdbc:postgresql://localhost:5432/test, setting catalog: foo
schema:information_schema
schema:pg_catalog
schema:public
```

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Tue, 04 Feb 2025 16:52:26 +0000

Please try in below way

**Jdbc url:** jdbc:postgresql://localhost:5432/**foo**

Now call **getSchemas("test")**

See if you are getting schemas of "test" in the response.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 17:47:44 +0000

```
Connected to jdbc:postgresql://localhost:5432/foo, setting catalog: test
schema:blah
schema:information_schema
schema:pg_catalog
schema:public
```

These are the correct schemas for foo. You can't change to the catalog test in postgres since that would be making a new connection.

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Wed, 05 Feb 2025 03:55:08 +0000

Ok but in that case getCatalogs() should not return all catalogs right? It should return only foo. Isn't it?

Also can you please check the same behaviour of both getCatalogs() and getSchemas() once in **postgresql-42.6.0.jar** as this version provides the schemas of "test" catalog when passed as input.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 10:43:41 +0000

42.6.0 Still providing catalogs from foo, not test
```
Connected to jdbc:postgresql://localhost:5432/foo, setting catalog: test
schema:blah
schema:information_schema
schema:pg_catalog
schema:public
```

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Wed, 05 Feb 2025 17:54:22 +0000

In your example, you are retrieving schemas.
Can you please post the response of getCatalogs() method from both 42.6 and 42.7 ?

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Mon, 10 Feb 2025 16:11:45 +0000

Hi,
Any update please?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 10 Feb 2025 16:21:59 +0000

42.7.5
getCatalogs()
```
Connected to jdbc:postgresql://localhost:5432/foo
catalog:contrib_regression
catalog:foo
catalog:postgres
catalog:template1
catalog:test
```
42.6.0
```
Connected to jdbc:postgresql://localhost:5432/foo
catalog:foo
```

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Mon, 10 Feb 2025 17:16:47 +0000

Exactly. This is what I was pointing to.
42.7.5 gives me all catalogs even though I have given "foo" in JDBC url where as 42.6 gives me only "foo" which in my opinion is the right behaviour.

So is this a bug in 42.7 ?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 10 Feb 2025 19:39:43 +0000

Yes, this is a bug.
PR's are welcome

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Tue, 11 Feb 2025 04:30:17 +0000

Please tell me the process to raise PR?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 11 Feb 2025 10:08:50 +0000

So I've given this some thought and I'm not sure this is a bug. It was changed https://github.com/pgjdbc/pgjdbc/commit/9ea59bd87f7920f1c2aba20ffef15acef85def60 

I thought you originally said this was OK ?

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Mon, 17 Feb 2025 06:39:07 +0000

Ok So I mentioned earlier that it was OK assuming that getSchemas() work based on the catalog passed in input which is not the case. 

Let me explain my use-case.

In our UI - we show catalog and schema.
Based on the catalog selected, it's corresponding schema's will be shown.

Until 42.6 version, it was working perfectly as only the catalog passed in JDBC url was appearing and it's corresponding schemas's were shown but after we migrated to 42.7, it broke.

UI shows multiple catalogs but whichever option you select, it only fetches the schema which is passed in JDBC url which is wrong.

So In my opinion, either we need to fix getCatalogs() to get the proper catalog OR if we decide to get all catalogs from DB then their corresponding schemas should be fetched.
Either of the one needs to be fixed.

Please look into it and if you need PR for this please let me know the process so that I can create one.

Thanks.

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Tue, 18 Mar 2025 16:04:00 +0000

Any update here please?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Mar 2025 11:24:44 +0000

I've given this some thought and I agree. I think the term database means the database that you connected to. This means that we should only return the catalogs that are available in that database.
@vlsi thoughts ?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 19 Mar 2025 15:01:01 +0000

> I think the term database means the database that you connected to

The problem is that "database" might mean different things there. For instance, it could be "server process" (==PG cluster), and it could be "PG database" (one of `select * from pg_database`).

As far as I understand, in PostgreSQL `users` (==`schema`) do not belong to `databases` (==`catalog`), and it looks like `getSchemas(String catalog` should ignore `catalog` parameter altogether.

---

> Based on the catalog selected, it's corresponding schema's will be shown

@avinashyn , could you clarify what do you mean by "corresponding" schemas for a catalog in PostgreSQL terms?

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Wed, 19 Mar 2025 16:02:39 +0000

@vlsi - please find the example from @davecramer comment above - https://github.com/pgjdbc/pgjdbc/issues/3504#issuecomment-2648573383

Please let me know if you need further clarifications?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 20 Mar 2025 10:13:58 +0000

@avinashyn So the question is how should we return the schemas ?

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Thu, 20 Mar 2025 13:22:34 +0000

Expectation here is w.r.t catalogs. 
Putting down your example

 jdbc:postgresql://localhost:5432/**foo**
Here getCatalogs() returns "foo" alone with 42.6.0 where as with 42.7 it returns

- catalog:contrib_regression
- catalog:foo
- catalog:postgres
- catalog:template1
- catalog:test

Expectation here is that it returns only "foo" even with 42.7 driver similar to 42.6 as that is the one passed in JDBC URL. 
So the way 42.6 driver version was working was correct IMO.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 20 Mar 2025 13:25:54 +0000

@avinashyn , could you please clarify why do you think `getCatalogs` should return a single entry only?

---

From: "avinashyn (@avinashyn)" <[email-masked]>
Date: Thu, 20 Mar 2025 13:42:12 +0000

Yes @vlsi - expectation is that getCatalogs() should return only the one passed in JDBC URL. 
While constructing the JDBC url, we are passing the "foo" catalog but while retrieving we are fetching other catalogs also.  Seems incorrect to me.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 20 Mar 2025 14:29:44 +0000

> expectation is that getCatalogs() should return only the one passed in JDBC URL

If you want something "passed to JDBC URL", then you should consider `String java.sql.Connection#getCatalog()`

Have you tried it? Does it answer your needs?