Subject: [pgjdbc/pgjdbc] issue #538: Why getSchemaName return empty string or exists getBaseSchemaName and getColumnName return getColumnLabel?

From: "arturgspb (@arturgspb)" <[email-masked]>
Date: Sun, 27 Mar 2016 13:42:47 +0000

Hello!

I have a problem in the integration of pgjdbc with spring jdbc. Spring JDBC code:
https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/support/rowset/ResultSetWrappingSqlRowSetMetaData.java

Spring encapsulates ResultSetMetaData and call it method getSchemaName, but pgjdbc return empty string. Why pgjdbc not proxy call getBaseSchemaName? getTableName have proxy call getBaseTableName.

getColumnName real return getColumnLabel, but I think will better if return getBaseColumnName.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 27 Mar 2016 13:46:09 +0000

Because the backend doesn't send us the schema name

Dave Cramer

On 27 March 2016 at 09:42, Artur Geraschenko [email-masked]
wrote:

> Spring encapsulates ResultSetMetaData and call it method getSchemaName,
> but pgjdbc return empty string. Why pgjdbc not proxy call
> getBaseSchemaName? getTableName have proxy call getBaseTableName.
> 
> —
> You are receiving this because you are subscribed to this thread.
> Reply to this email directly or view it on GitHub
> https://github.com/pgjdbc/pgjdbc/issues/538

---

From: "arturgspb (@arturgspb)" <[email-masked]>
Date: Sun, 27 Mar 2016 13:49:11 +0000

@davecramer, not sure I understand you. getBaseSchemaName contains the actual schema.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 27 Mar 2016 13:54:06 +0000

It will likely still return "".

see
https://github.com/pgjdbc/pgjdbc/blob/2d5e7fa57045a47887de4ea300a743a160ae4f86/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ResultSetMetaDataTest.java#L109

Dave Cramer

On 27 March 2016 at 09:49, Artur Geraschenko [email-masked]
wrote:

> @davecramer https://github.com/davecramer, not sure I understand you.
> getBaseSchemaName contains the actual schema.
> 
> —
> You are receiving this because you were mentioned.
> https://github.com/pgjdbc/pgjdbc/issues/538#issuecomment-202066955

---

From: "arturgspb (@arturgspb)" <[email-masked]>
Date: Sun, 27 Mar 2016 14:09:04 +0000

Example code:

``` java
package ru.realweb.meta;

import org.apache.commons.dbcp2.BasicDataSource;
import org.postgresql.jdbc.PgResultSetMetaData;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;

public class Test {

    public static void main(String[] args) throws SQLException {
        String driverClassName = "org.postgresql.Driver";
        String schema = "postgresql";
        String urlAdditional = "?encoding=UNICODE&ApplicationName=app&allowMultiQueries=true";

        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(driverClassName);
        String hostPort = "***:5432/";
        String url = "jdbc:" + schema + "://" + hostPort + "***" + urlAdditional;
        ds.setUrl(url);
        ds.setUsername("***");
        ds.setPassword("***");
        ds.setMaxTotal(50);
        ds.setMaxIdle(5);

        String sqlQuery = "select client_id as my_client_id, price as value from client_stats limit 10";

        System.out.println("*** Pure JDBC");
        PreparedStatement ps = ds.getConnection().prepareStatement(sqlQuery);
        ResultSetMetaData jdbcMetaData = ps.getMetaData();
        for (int i = 1; i <= jdbcMetaData.getColumnCount(); i++) {
            System.out.println("\n");
            System.out.println("jdbcMetaData.getColumnLabel() = " + jdbcMetaData.getColumnLabel(i));

            if (jdbcMetaData instanceof PgResultSetMetaData) {
                PgResultSetMetaData pgmd = (PgResultSetMetaData) jdbcMetaData;
                System.out.println("pgmd.getColumnName = " + pgmd.getBaseColumnName(i));
                System.out.println("pgmd.getBaseTableName = " + pgmd.getBaseTableName(i));
                System.out.println("pgmd.getBaseSchemaName = " + pgmd.getBaseSchemaName(i));
            }
        }

        System.out.println("\n\n");
        System.out.println("*** Spring JDBC");
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(ds);
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(transactionManager.getDataSource());
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sqlQuery, new HashMap<>());
        SqlRowSetMetaData springMetaData = sqlRowSet.getMetaData();
        for (int i = 1; i <= springMetaData.getColumnCount(); i++) {
            System.out.println("\n");
            System.out.println("springMetaData.getColumnLabel(i) = " + springMetaData.getColumnLabel(i));
            System.out.println("springMetaData.getColumnName(i) = " + springMetaData.getColumnName(i));
            System.out.println("springMetaData.getTableName(i) = " + springMetaData.getTableName(i));
            System.out.println("springMetaData.getSchemaName(i) = " + springMetaData.getSchemaName(i));
        }
    }
}
```

Will print:

``` plain
*** Pure JDBC

jdbcMetaData.getColumnLabel() = my_client_id
pgmd.getColumnName = client_id
pgmd.getBaseTableName = client_stats
pgmd.getBaseSchemaName = public

jdbcMetaData.getColumnLabel() = value
pgmd.getColumnName = price
pgmd.getBaseTableName = client_stats
pgmd.getBaseSchemaName = public

*** Spring JDBC

springMetaData.getColumnLabel(i) = my_client_id
springMetaData.getColumnName(i) = my_client_id
springMetaData.getTableName(i) = client_stats
springMetaData.getSchemaName(i) = 

springMetaData.getColumnLabel(i) = value
springMetaData.getColumnName(i) = value
springMetaData.getTableName(i) = client_stats
springMetaData.getSchemaName(i) = 
```

depends: 
org.springframework:spring-jdbc:4.2.5.RELEASE
org.postgresql:postgresql:9.4.1208

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 27 Mar 2016 14:20:27 +0000

OK, I see the challenge.

As I said the server does not provide this information unless we ask for
it. getSchemaName would have to do a query to get the name. I seem to
recall we did not want to add a performance regression.

Doesn't spring have a "Dialect" like hibernate ?

Dave Cramer

On 27 March 2016 at 10:09, Artur Geraschenko [email-masked]
wrote:

> Example code:
> 
> package ru.realweb.meta;
> import org.apache.commons.dbcp2.BasicDataSource;import org.postgresql.jdbc.PgResultSetMetaData;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import org.springframework.jdbc.support.rowset.SqlRowSet;import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
> import java.sql.PreparedStatement;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.HashMap;
> public class Test {
> 
> ```
> **\* Pure JDBC
> **\* Spring JDBC
> https://github.com/pgjdbc/pgjdbc/issues/538#issuecomment-202069493

---

From: "arturgspb (@arturgspb)" <[email-masked]>
Date: Sun, 27 Mar 2016 14:28:12 +0000

Unfortunately, I do not know. But there is the matter of performance, why this method works as a proxy?
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java#L335

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 27 Mar 2016 14:38:27 +0000

So back to your original question. What is your problem? I see spring just
calls getSchemaName. The spec says it's fine to return ""

Dave Cramer

On 27 March 2016 at 10:28, Artur Geraschenko [email-masked]
wrote:

> https://github.com/pgjdbc/pgjdbc/issues/538#issuecomment-202072312

---

From: "arturgspb (@arturgspb)" <[email-masked]>
Date: Sun, 27 Mar 2016 14:57:08 +0000

I would like not interfering in the Spring mechanisms and when calling getSchemaName get the real schema name. And well as receiving a call getColumnName real column name and not the column alias.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Mon, 28 Mar 2016 07:08:37 +0000

It looks like it would be fine if org.postgresql.jdbc.PgResultSetMetaData#getSchemaName delegates to org.postgresql.jdbc.PgResultSetMetaData#getBaseSchemaName.

> And well as receiving a call getColumnName real column name and not the column alias

That seems feasible.

@arturgspb , would you like to prepare some PR to fix those issues?

---

From: "arturgspb (@arturgspb)" <[email-masked]>
Date: Mon, 28 Mar 2016 07:14:50 +0000

@vlsi, yes, of course, I am happy to do it.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 28 Mar 2016 11:06:37 +0000

Well I am still concerned what this is going to break. For some reason the
old code didn't do this.

Dave Cramer

On 28 March 2016 at 03:14, Artur Geraschenko [email-masked]
wrote:

> Yes, of course, I am happy to do it.
> 
> —
> https://github.com/pgjdbc/pgjdbc/issues/538#issuecomment-202275709

---

From: "hiro2k (@hiro2k)" <[email-masked]>
Date: Wed, 04 May 2016 18:09:47 +0000

I've had this patched into my jdbc driver for a long time and have not had any issues with it. There was some concern that column aliases would not be returned properly because that information is not sent from the db, but the base column name should be there and it's better to have those than just an empty string.

---

From: "tapioko (@tapioko)" <[email-masked]>
Date: Mon, 12 Aug 2019 14:12:19 +0000

Netbeans is affected by getSchemaName returning an empty string: https://issues.apache.org/jira/browse/NETBEANS-1140

---

From: "lukaseder (@lukaseder)" <[email-masked]>
Date: Mon, 12 Dec 2022 09:57:27 +0000

I wonder if this could be implemented relatively easily by now? It was implemented for `PgResultSetMetaData::getTableName`, in case of which a query like this one is issued:

```sql
SELECT
  c.oid,
  a.attnum,
  a.attname,
  c.relname,
  n.nspname,
  a.attnotnull OR (t.typtype = 'd' AND t.typnotnull),
  a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%'
FROM
  pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON
  (c.relnamespace = n.oid)
JOIN pg_catalog.pg_attribute a ON
  (c.oid = a.attrelid)
JOIN pg_catalog.pg_type t ON
  (a.atttypid = t.oid)
LEFT JOIN pg_catalog.pg_attrdef d ON
  (d.adrelid = a.attrelid
    AND d.adnum = a.attnum)
JOIN (...) vals ON
  (c.oid = vals.oid AND a.attnum = vals.attnum)
```

That query produces the schema name (`n.nspname`) and the information is cached. Since users are likely going to call `PgResultSetMetaData::getTableName` as well when they call `PgResultSetMetaData::getSchemaName`, I suspect there wouldn't be any additional overhead?

---

From: "lukaseder (@lukaseder)" <[email-masked]>
Date: Mon, 12 Dec 2022 10:00:53 +0000

In fact, `PgResultSetMetaData::getBaseSchemaName` is already implemented. It's just not being called by `PgResultSetMetaData::getSchemaName`

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 12 Dec 2022 13:05:57 +0000

Well it can but most people don't really wan't to take the hit of doing another round trip. My admittedly fuzzy recollection here is that folks expected the schema name to be returned by the server in the results.

---

From: "lukaseder (@lukaseder)" <[email-masked]>
Date: Mon, 12 Dec 2022 14:07:34 +0000

> Well it can but most people don't really wan't to take the hit of doing another round trip

But the result is cached in `BaseConnection::getFieldMetadataCache`, so between the various calls to `PgResultSetMetaData`, the extra round trip is shared as good as possible. Given that a round trip is highly likely already, I think that most people will not mind?

> My admittedly fuzzy recollection here is that folks expected the schema name to be returned by the server in the results.

That would be much better, no doubt. But apparently, this information isn't already present now.

Anyway, I just wanted to add this information here, given that I had to look it up myself and that I haven't seen anyone else mention the existing cached data from other round trips.

---

From: "prrvchr (@prrvchr)" <[email-masked]>
Date: Sat, 02 Aug 2025 20:16:47 +0000

Hi all,

I'm surprised by this problem and apparently it's still not fixed.
Is there anything planned?

Thank you.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 02 Aug 2025 20:20:16 +0000

Well, by default the resultset does not have the schema name in it.

---

From: "prrvchr (@prrvchr)" <[email-masked]>
Date: Sat, 02 Aug 2025 20:40:28 +0000

And maybe there could be a connection option so that this is filled in for example.

With several schemas I don't see how this can work correctly as is?

In fact, this would allow us to support [javax.sql.rowset.CachedRowSet](https://docs.oracle.com/javase/8/docs/api/javax/sql/rowset/CachedRowSet.html) in a slightly less cumbersome way.

---

From: "prrvchr (@prrvchr)" <[email-masked]>
Date: Tue, 05 Aug 2025 11:33:49 +0000

Well, I have exactly the same problem with the Oracle JDBC driver ojdbc17.jar. Their ResultSets don't provide the table name or schema name in the metadata. Well done, Oracle...

But if I take the trouble to find the table name in the SQL query and make sure it is a `SELECT` query that only looks at one table, then I can find the missing data and make this ResultSet editable in LibreOffice Base.

I'm not going to claim anything from Oracle, that would be a waste of time, and neither here, but I would like to point out that analyzing the query in order to extract the name of the table should be part of the JDBC driver (if it is supposed to be called that).

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 05 Aug 2025 14:54:02 +0000

@prrvchr the driver doesn't really analyze queries. It just passes them through. Analyzing queries would be quite expensive.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 05 Aug 2025 15:04:00 +0000

Should we rather infer the schema name like Lukas suggested in https://github.com/pgjdbc/pgjdbc/issues/538#issuecomment-1346193264?

Sure think we should not try parsing SQL to infer the table names. However, I think we should retrieve the schema name along with the table name if we can do that.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 05 Aug 2025 19:40:15 +0000

So I was able to get the following to work 
```try (ResultSet resultSet = statement.executeQuery("select  foo.a.i,public.a.i from foo.a, a;")) {
                    ResultSetMetaData metaData = resultSet.getMetaData();
                    String schema1 = metaData.getSchemaName(1);
                    String table1 = metaData.getTableName(1);
                    System.out.println("Column 1 is from table: " + table1 + " Schema: " + schema1);
                    String schema2 = metaData.getSchemaName(2);
                    String table2 = metaData.getTableName(2);
                    System.out.println("Column 2 is from table: " + table2 + " Schema: " + schema2);
```
the output
```
Column 1 is from table: a Schema: foo
Column 2 is from table: a Schema: public
```

with the following change 
```diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java b/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java
index 46ed0e60..d31e3a41 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSetMetaData.java
@@ -172,7 +172,13 @@ public class PgResultSetMetaData implements ResultSetMetaData, PGResultSetMetaDa
 
   @Override
   public String getSchemaName(int column) throws SQLException {
-    return "";
+    Field field = getField(column);
+    if (field.getTableOid() == 0) {
+      return "";
+    }
+    fetchFieldMetaData();
+    FieldMetadata metadata = field.getMetadata();
+    return metadata == null ? "" : metadata.schemaName;
   }
 
   private boolean populateFieldsWithMetadata(Gettable<FieldMetadata.Key, FieldMetadata> metadata) {
```

Seems this is not that difficult. One question though. Do we want to return `public` or just leave it empty ?

---

From: "prrvchr (@prrvchr)" <[email-masked]>
Date: Tue, 05 Aug 2025 20:20:13 +0000

> Seems this is not that difficult.

If it's that simple, I think it's worth implementing.
All of this is nothing compared to the code needed to parse queries and complete the missing metadata for a ResultSet.

> One question though. Do we want to return `public` or just leave it empty ?

What are the cases where we cannot find the schema?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 05 Aug 2025 21:20:31 +0000

> If it's that simple, I think it's worth implementing. All of this is nothing compared to the code needed to parse queries and complete the missing metadata for a ResultSet.
Yes, agreed. 
> What are the cases where we cannot find the schema?

`Select 1 as foo` will not provide a schema

---

From: "prrvchr (@prrvchr)" <[email-masked]>
Date: Wed, 06 Aug 2025 01:41:55 +0000

> `Select 1 as foo` will not provide a schema

Okay, these columns are of little interest for updating and should normally be read-only.
An empty string seems to me to be the most suitable.

---

From: "lukaseder (@lukaseder)" <[email-masked]>
Date: Wed, 06 Aug 2025 08:51:05 +0000

> One question though. Do we want to return public or just leave it empty ?

The `ResultSetMetaData::getSchemaName` Javadoc says *"name or "" if not applicable"*

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 06 Aug 2025 09:20:36 +0000

> The `ResultSetMetaData::getSchemaName` Javadoc says _"name or "" if not applicable"_

The question is more about whether to add `public` since the original query did not have it and it's not necessary to create a query from it. Technically it is correct but seems odd to me. I would vote to return an empty string instead

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 06 Aug 2025 15:30:34 +0000

PR to fix this #3753