pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
25+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-01-31 09:14 "avinashyn (@avinashyn)" <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-01-31 09:14 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-01-31 13:23 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-01-31 13:23 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Interesting, can you try with the latest 42.7.5 ?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-03 04:38 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-03 04:38 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-04 15:58 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-02-04 15:58 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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);
}
rs = conn.getMetaData().getSchemas();
while (rs.next())
{
String schema = rs.getString("TABLE_SCHEM");
System.out.println("schema:"+schema);
}
}
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
```
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-04 16:52 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-04 16:52 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-04 17:47 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-02-04 17:47 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
```
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-05 03:55 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-05 03:55 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-05 10:43 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-02-05 10:43 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
```
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-05 17:54 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-05 17:54 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
In your example, you are retrieving schemas.
Can you please post the response of getCatalogs() method from both 42.6 and 42.7 ?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-10 16:11 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-10 16:11 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Hi,
Any update please?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-10 16:21 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-02-10 16:21 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
```
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-10 17:16 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-10 17:16 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 ?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-10 19:39 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-02-10 19:39 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Yes, this is a bug.
PR's are welcome
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-11 04:30 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-11 04:30 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Please tell me the process to raise PR?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-11 10:08 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-02-11 10:08 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 ?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-02-17 06:39 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-02-17 06:39 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-18 16:04 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-03-18 16:04 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Any update here please?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-19 11:24 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-03-19 11:24 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 ?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-19 15:01 "vlsi (@vlsi)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: vlsi (@vlsi) @ 2025-03-19 15:01 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-19 16:02 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-03-19 16:02 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-20 10:13 "davecramer (@davecramer)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: davecramer (@davecramer) @ 2025-03-20 10:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@avinashyn So the question is how should we return the schemas ?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-20 13:22 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-03-20 13:22 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-20 13:25 "vlsi (@vlsi)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: vlsi (@vlsi) @ 2025-03-20 13:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@avinashyn , could you please clarify why do you think `getCatalogs` should return a single entry only?
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-20 13:42 "avinashyn (@avinashyn)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: avinashyn (@avinashyn) @ 2025-03-20 13:42 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input
@ 2025-03-20 14:29 "vlsi (@vlsi)" <[email protected]>
23 siblings, 0 replies; 25+ messages in thread
From: vlsi (@vlsi) @ 2025-03-20 14:29 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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?
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2025-03-20 14:29 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-31 09:14 [pgjdbc/pgjdbc] issue #3504: Wrong schemas fetched for a given catalog as input "avinashyn (@avinashyn)" <[email protected]>
2025-01-31 13:23 ` "davecramer (@davecramer)" <[email protected]>
2025-02-03 04:38 ` "avinashyn (@avinashyn)" <[email protected]>
2025-02-04 15:58 ` "davecramer (@davecramer)" <[email protected]>
2025-02-04 16:52 ` "avinashyn (@avinashyn)" <[email protected]>
2025-02-04 17:47 ` "davecramer (@davecramer)" <[email protected]>
2025-02-05 03:55 ` "avinashyn (@avinashyn)" <[email protected]>
2025-02-05 10:43 ` "davecramer (@davecramer)" <[email protected]>
2025-02-05 17:54 ` "avinashyn (@avinashyn)" <[email protected]>
2025-02-10 16:11 ` "avinashyn (@avinashyn)" <[email protected]>
2025-02-10 16:21 ` "davecramer (@davecramer)" <[email protected]>
2025-02-10 17:16 ` "avinashyn (@avinashyn)" <[email protected]>
2025-02-10 19:39 ` "davecramer (@davecramer)" <[email protected]>
2025-02-11 04:30 ` "avinashyn (@avinashyn)" <[email protected]>
2025-02-11 10:08 ` "davecramer (@davecramer)" <[email protected]>
2025-02-17 06:39 ` "avinashyn (@avinashyn)" <[email protected]>
2025-03-18 16:04 ` "avinashyn (@avinashyn)" <[email protected]>
2025-03-19 11:24 ` "davecramer (@davecramer)" <[email protected]>
2025-03-19 15:01 ` "vlsi (@vlsi)" <[email protected]>
2025-03-19 16:02 ` "avinashyn (@avinashyn)" <[email protected]>
2025-03-20 10:13 ` "davecramer (@davecramer)" <[email protected]>
2025-03-20 13:22 ` "avinashyn (@avinashyn)" <[email protected]>
2025-03-20 13:25 ` "vlsi (@vlsi)" <[email protected]>
2025-03-20 13:42 ` "avinashyn (@avinashyn)" <[email protected]>
2025-03-20 14:29 ` "vlsi (@vlsi)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox