Subject: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`

From: "desruisseaux (@desruisseaux)" <[email-masked]>
Date: Tue, 24 Jun 2025 11:33:03 +0000

A query with the `ST_EstimatedExtent` spatial function works as expected when the query is executed on a connection created by `DriverManager`, but fails if the exact same query is executed on a connection created by `PGSimpleDataSource`. The query worked in both cases on PostgreSQL 15 and fails on PostgreSQL 16. The problem does not seem to depend on the JDBC driver version. Ordinary queries (without spatial functions) work in all cases.

## Environment

**Problem reproduced on:**
* PostgreSQL 16.9
* PostGIS 3.5.2
* JDBC driver 42.7.7
* Fedora Linux 42 (Workstation Edition)

**We verified that it was working on:**
* PostgreSQL 15.4
* PostGIS 3.3
* Same JDBC driver version and Linux environment

## Steps to reproduce

Creates an empty database named `SpatialMetadataTest` with the PostGIS extension. Execute the following SQL script:

```sql
SET search_path TO public;
CREATE SCHEMA features;
CREATE TABLE features."SpatialData" (
    "geometry" GEOMETRY
);
INSERT INTO features."SpatialData" ("geometry") VALUES (ST_GeomFromText('POINT(3 4)'));
ANALYZE;
```

Then, execute the following Java class. This code tests two queries:

* `SELECT geometry FROM features."SpatialData"` (succeed in all cases)
* `SELECT public.ST_EstimatedExtent('features', 'SpatialData', 'geometry')` (succeed with `DriverManager` but fails with `PGSimpleDataSource`).

```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.postgresql.ds.PGSimpleDataSource;

public class Test {
    public static void main(String[] args) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/SpatialMetadataTest")) {
            query(connection);
        }
        System.out.println("Success using DriverManager.");
        /*
         * Try again the same query, but using a connection created by PGSimpleDataSource.
         */
        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setDatabaseName("SpatialMetadataTest");
        ds.setCurrentSchema("features");
        try (Connection connection = ds.getConnection()) {
            query(connection);
        }
        System.out.println("Success using PGSimpleDataSource.");
    }

    private static void query(Connection connection) throws SQLException {
        try (Statement stmt = connection.createStatement()) {
            try (ResultSet rs = stmt.executeQuery("SELECT geometry FROM features.\"SpatialData\"")) {
                while (rs.next()) {
                    System.out.println("Result from SELECT: " + rs.getObject(1));
                }
            }
            try (ResultSet rs = stmt.executeQuery("SELECT public.ST_EstimatedExtent('features', 'SpatialData', 'geometry')")) {
                while (rs.next()) {
                    System.out.println("Result from ST_EstimatedExtent: " + rs.getObject(1));
                }
            }
        }
    }
}
```

## Result

Output is below. An _"features"."SpatialData"."geometry" must be a geometry or geography_ exception it thrown, but only on the test using `PGSimpleDataSource`. It was working with older PostgreSQL / PostGIS version.

```
Result from SELECT: 010100000000000000000008400000000000001040
Result from ST_EstimatedExtent: BOX(3 4,3 4)
Success using DriverManager.
Result from SELECT: 010100000000000000000008400000000000001040
Exception in thread "main" org.postgresql.util.PSQLException: ERREUR: column "features"."SpatialData"."geometry" must be a geometry or geography
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2734)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:518)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:435)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:357)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:342)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:318)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:267)
	at test.Test.query(Test.java:35)
	at test.Test.main(Test.java:23)
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 24 Jun 2025 11:36:32 +0000

I'm not sure how this is a pgjdbc problem if it works in version 15?

---

From: "desruisseaux (@desruisseaux)" <[email-masked]>
Date: Tue, 24 Jun 2025 12:09:25 +0000

Because it works with `DriverManager` but not with `PGSimpleDataSource`. So it seems to be a mix of pgjdbc and PostgreSQL / PostGIS issues.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 24 Jun 2025 12:13:27 +0000

right, but the driver didn't change between 15 and 16. Can you do the same query in psql ? I'd like to see the server logs ?

---

From: "desruisseaux (@desruisseaux)" <[email-masked]>
Date: Tue, 24 Jun 2025 12:31:16 +0000

Yes, the same query work in PGAdmin. It also works in NetBeans and IntelliJ, which I presume use `DriverManager`. The problem occurs only with `PGSimpleDataSource`. In the `/var/lib/pgsql/data/log/postgresql-Tue.log` file, I find nothing more than what the exception is saying:

```
2025-06-24 14:25:34.136 CEST [51615] LOG:  le système de bases de données est prêt pour accepter les connexions
2025-06-24 14:27:03.178 CEST [51974] ERREUR:  column "features"."SpatialData"."geometry" must be a geometry or geography
2025-06-24 14:27:03.178 CEST [51974] INSTRUCTION :  SELECT public.ST_EstimatedExtent('features', 'SpatialData', 'geometry')
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 24 Jun 2025 12:36:56 +0000

Well, without a way to replicate it I really can't help.

---

From: "desruisseaux (@desruisseaux)" <[email-masked]>
Date: Tue, 24 Jun 2025 12:39:14 +0000

The test case in the summary of this issue replicate it.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 24 Jun 2025 12:41:54 +0000

Well I require a postgres instance with PostGIS installed.

I suppose you could try turning on logging in the driver and we could look at the logs to see what is different.

Dave

---

From: "desruisseaux (@desruisseaux)" <[email-masked]>
Date: Tue, 24 Jun 2025 13:32:01 +0000

Below are the logs from the JDBC driver. For making comparison easier, I removed all values that vary at each execution (timestamp, hash codes in `toString()`, PID) and formatted the remaining as a table. I also removed the ordinary query for focussing on the error.

## When using `DriverManager`
```
Driver.loadDefaultProperties            : Loading driver configuration via classloader jdk.internal.loader.ClassLoaders$AppClassLoader
PgPassParser.findPgPasswordResourceName : Value for resource [pgpass] not found
Driver.connect                          : Connecting with URL: jdbc:postgresql://localhost:5432/SpatialMetadataTest
PgConnection.<init>                     : PostgreSQL JDBC Driver 42.7.7
PgConnection.setDefaultFetchSize        :   setDefaultFetchSize = 0
PgConnection.setPrepareThreshold        :   setPrepareThreshold = 5
ConnectionFactoryImpl.openConnectionImpl: Trying to establish a protocol version 3 connection to localhost:5432
Encoding.<init>                         : Creating new Encoding UTF-8 with fastASCIINumbers true
Encoding.<init>                         : Creating new Encoding UTF-8 with fastASCIINumbers true
ConnectionFactoryImpl.tryConnect        : Receive Buffer Size is 65 536
ConnectionFactoryImpl.tryConnect        : Send Buffer Size is 1 313 280
ConnectionFactoryImpl.enableSSL         :  FE=> SSLRequest postgres
ConnectionFactoryImpl.enableSSL         :  <=BE SSLRefused
ConnectionFactoryImpl.sendStartupPacket :  FE=> StartupPacket(user=desruisseaux, database=SpatialMetadataTest, client_encoding=UTF8, DateStyle=ISO, TimeZone=Europe/Paris)
ConnectionFactoryImpl.doAuthentication  :  <=BE AuthenticationOk
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(in_hot_standby = off)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(integer_datetimes = on)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(TimeZone = Europe/Paris)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(IntervalStyle = postgres)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(is_superuser = on)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(application_name = )
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(default_transaction_read_only = off)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(scram_iterations = 4096)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(DateStyle = ISO, YMD)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(standard_conforming_strings = on)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(session_authorization = desruisseaux)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(client_encoding = UTF8)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(server_version = 16.9)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(server_encoding = UTF8)
QueryExecutorImpl.readStartupMessages   :  <=BE BackendKeyData(pid=...,ckey=[B)
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
QueryExecutorImpl.execute               :   simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler, maxRows=0, fetchSize=0, flags=1 047
QueryExecutorImpl.sendSimpleQuery       :  FE=> SimpleQuery(query="SET application_name = 'PostgreSQL JDBC Driver'")
QueryExecutorImpl.receiveCommandStatus  :  <=BE CommandStatus(SET)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(application_name = PostgreSQL JDBC Driver)
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
PgConnection.<init>                     :     types using binary send = TIMESTAMPTZ,NUMERIC,OID_ARRAY,UUID,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
PgConnection.<init>                     :     types using binary receive = TIMESTAMPTZ,NUMERIC,OID_ARRAY,UUID,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,DATE,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
PgConnection.<init>                     :     integer date/time = true
QueryExecutorImpl.execute               :   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler, maxRows=0, fetchSize=0, flags=17
QueryExecutorImpl.sendParse             :  FE=> Parse(stmt=null,query="SELECT public.ST_EstimatedExtent('features', 'SpatialData', 'geometry')",oids={})
QueryExecutorImpl.sendBind              :  FE=> Bind(stmt=null,portal=null)
QueryExecutorImpl.sendDescribePortal    :  FE=> Describe(portal=null)
QueryExecutorImpl.sendExecute           :  FE=> Execute(portal=null,limit=0)
QueryExecutorImpl.sendSync              :  FE=> Sync
QueryExecutorImpl.processResults        :  <=BE ParseComplete [null]
QueryExecutorImpl.processResults        :  <=BE BindComplete [unnamed]
QueryExecutorImpl.receiveFields         :  <=BE RowDescription(1)
QueryExecutorImpl.receiveFields         :         Field(st_estimatedextent,<unknown:18074>,65,T)
QueryExecutorImpl.processResults        :  <=BE DataRow(len=12)
QueryExecutorImpl.receiveCommandStatus  :  <=BE CommandStatus(SELECT 1)
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
PgResultSet.getObject                   :   getObject columnIndex: 1
QueryExecutorImpl.execute               :   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler, maxRows=0, fetchSize=0, flags=17
QueryExecutorImpl.sendParse             :  FE=> Parse(stmt=null,query="SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = $1",oids={23})
QueryExecutorImpl.sendBind              :  FE=> Bind(stmt=null,portal=null,$1=<('18074'::int4)>,type=INT4)
QueryExecutorImpl.sendDescribePortal    :  FE=> Describe(portal=null)
QueryExecutorImpl.sendExecute           :  FE=> Execute(portal=null,limit=0)
QueryExecutorImpl.sendSync              :  FE=> Sync
QueryExecutorImpl.processResults        :  <=BE ParseComplete [null]
QueryExecutorImpl.processResults        :  <=BE BindComplete [unnamed]
QueryExecutorImpl.receiveFields         :  <=BE RowDescription(3)
QueryExecutorImpl.receiveFields         :         Field(?column?,BOOL,1,T)
QueryExecutorImpl.receiveFields         :         Field(nspname,NAME,64,T)
QueryExecutorImpl.receiveFields         :         Field(typname,NAME,64,T)
QueryExecutorImpl.processResults        :  <=BE DataRow(len=12)
QueryExecutorImpl.receiveCommandStatus  :  <=BE CommandStatus(SELECT 1)
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
PgResultSet.getBoolean                  :   getBoolean columnIndex: 1
PgResultSet.getString                   :   getString columnIndex: 2
PgResultSet.getString                   :   getString columnIndex: 3
TypeInfoCache.getSQLType                : querying SQL typecode for pg type oid '18 074'
QueryExecutorImpl.execute               :   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler, maxRows=0, fetchSize=0, flags=17
QueryExecutorImpl.sendParse             :  FE=> Parse(stmt=null,query="SELECT typinput='pg_catalog.array_in'::regproc as is_array, typtype, typname, pg_type.oid   FROM pg_catalog.pg_type   LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE pg_type.oid = $1  ORDER BY sp.r, pg_type.oid DESC",oids={20})
QueryExecutorImpl.sendBind              :  FE=> Bind(stmt=null,portal=null,$1=<('18074'::int8)>,type=INT8)
QueryExecutorImpl.sendDescribePortal    :  FE=> Describe(portal=null)
QueryExecutorImpl.sendExecute           :  FE=> Execute(portal=null,limit=0)
QueryExecutorImpl.sendSync              :  FE=> Sync
QueryExecutorImpl.processResults        :  <=BE ParseComplete [null]
QueryExecutorImpl.processResults        :  <=BE BindComplete [unnamed]
QueryExecutorImpl.receiveFields         :  <=BE RowDescription(4)
QueryExecutorImpl.receiveFields         :         Field(is_array,BOOL,1,T)
QueryExecutorImpl.receiveFields         :         Field(typtype,CHAR,1,T)
QueryExecutorImpl.receiveFields         :         Field(typname,NAME,64,T)
QueryExecutorImpl.receiveFields         :         Field(oid,OID,4,T)
QueryExecutorImpl.processResults        :  <=BE DataRow(len=12)
QueryExecutorImpl.receiveCommandStatus  :  <=BE CommandStatus(SELECT 1)
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
PgResultSet.getBoolean                  :   getBoolean columnIndex: 1
PgResultSet.getString                   :   getString columnIndex: 2
PgResultSet.getString                   :   getString columnIndex: 1
PgConnection.getObject                  : Constructing object from type=box2d value=<BOX(3 4,3 4)>
QueryExecutorCloseAction.close          :  FE=> Terminate
```

## When uting `PGSimpleDataSource`
```
PgPassParser.findPgPasswordResourceName : Value for resource [pgpass] not found
Driver.connect                          : Connecting with URL: jdbc:postgresql://localhost/SpatialMetadataTest?currentSchema=features
PgConnection.<init>                     : PostgreSQL JDBC Driver 42.7.7
PgConnection.setDefaultFetchSize        :   setDefaultFetchSize = 0
PgConnection.setPrepareThreshold        :   setPrepareThreshold = 5
ConnectionFactoryImpl.openConnectionImpl: Trying to establish a protocol version 3 connection to localhost:5432
ConnectionFactoryImpl.tryConnect        : Receive Buffer Size is 65 536
ConnectionFactoryImpl.tryConnect        : Send Buffer Size is 1 313 280
ConnectionFactoryImpl.enableSSL         :  FE=> SSLRequest postgres
ConnectionFactoryImpl.enableSSL         :  <=BE SSLRefused
ConnectionFactoryImpl.sendStartupPacket :  FE=> StartupPacket(user=desruisseaux, database=SpatialMetadataTest, client_encoding=UTF8, DateStyle=ISO, TimeZone=Europe/Paris, search_path=features)
ConnectionFactoryImpl.doAuthentication  :  <=BE AuthenticationOk
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(in_hot_standby = off)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(integer_datetimes = on)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(TimeZone = Europe/Paris)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(IntervalStyle = postgres)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(is_superuser = on)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(application_name = )
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(default_transaction_read_only = off)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(scram_iterations = 4096)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(DateStyle = ISO, YMD)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(standard_conforming_strings = on)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(session_authorization = desruisseaux)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(client_encoding = UTF8)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(server_version = 16.9)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(server_encoding = UTF8)
QueryExecutorImpl.readStartupMessages   :  <=BE BackendKeyData(pid=...,ckey=[B)
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
QueryExecutorImpl.execute               :   simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler, maxRows=0, fetchSize=0, flags=1 047
QueryExecutorImpl.sendSimpleQuery       :  FE=> SimpleQuery(query="SET application_name = 'PostgreSQL JDBC Driver'")
QueryExecutorImpl.receiveCommandStatus  :  <=BE CommandStatus(SET)
QueryExecutorImpl.receiveParameterStatus:  <=BE ParameterStatus(application_name = PostgreSQL JDBC Driver)
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
PgConnection.<init>                     :     types using binary send = TIMESTAMPTZ,NUMERIC,OID_ARRAY,UUID,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
PgConnection.<init>                     :     types using binary receive = TIMESTAMPTZ,NUMERIC,OID_ARRAY,UUID,BYTEA_ARRAY,INT2_ARRAY,INT4_ARRAY,BYTEA,TEXT_ARRAY,TIMETZ,INT8,INT2,INT4,VARCHAR_ARRAY,INT8_ARRAY,POINT,DATE,TIMESTAMP,TIME,BOX,FLOAT4,FLOAT8,FLOAT4_ARRAY,FLOAT8_ARRAY
PgConnection.<init>                     :     integer date/time = true
BaseDataSource.getConnection            : Created a Non-Pooling DataSource from PostgreSQL JDBC Driver 42.7.7 for null at jdbc:postgresql://localhost/SpatialMetadataTest?currentSchema=features
QueryExecutorImpl.execute               :   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler, maxRows=0, fetchSize=0, flags=17
QueryExecutorImpl.sendParse             :  FE=> Parse(stmt=null,query="SELECT public.ST_EstimatedExtent('features', 'SpatialData', 'geometry')",oids={})
QueryExecutorImpl.sendBind              :  FE=> Bind(stmt=null,portal=null)
QueryExecutorImpl.sendDescribePortal    :  FE=> Describe(portal=null)
QueryExecutorImpl.sendExecute           :  FE=> Execute(portal=null,limit=0)
QueryExecutorImpl.sendSync              :  FE=> Sync
QueryExecutorImpl.processResults        :  <=BE ParseComplete [null]
QueryExecutorImpl.processResults        :  <=BE BindComplete [unnamed]
QueryExecutorImpl.receiveFields         :  <=BE RowDescription(1)
QueryExecutorImpl.receiveFields         :         Field(st_estimatedextent,<unknown:18074>,65,T)
QueryExecutorImpl.receiveErrorResponse  :  <=BE ErrorMessage(ERREUR: column "features"."SpatialData"."geometry" must be a geometry or geography
QueryExecutorImpl.receiveRFQ            :  <=BE ReadyForQuery(I)
QueryExecutorImpl.processResults        :  FE marking setPortalDescribed(false) for query SYNC
QueryExecutorCloseAction.close          :  FE=> Terminate
```

## Differences
Lines present with `DriverManager` but not with `PGSimpleDataSource` (ignoring the lines that occur after the error):

```
Driver.loadDefaultProperties : Loading driver configuration via classloader jdk.internal.loader.ClassLoaders$AppClassLoader
Encoding.<init>              : Creating new Encoding UTF-8 with fastASCIINumbers true
Encoding.<init>              : Creating new Encoding UTF-8 with fastASCIINumbers true
```

Lines present with `PGSimpleDataSource` but not with `DriverManager`:
```
BaseDataSource.getConnection           : Created a Non-Pooling DataSource from PostgreSQL JDBC Driver 42.7.7 for null at jdbc:postgresql://localhost/SpatialMetadataTest?currentSchema=features
QueryExecutorImpl.receiveErrorResponse :  <=BE ErrorMessage(ERREUR: column "features"."SpatialData"."geometry" must be a geometry or geography
```

Lines that are different:
```
Driver.connect : Connecting with URL: jdbc:postgresql://localhost:5432/SpatialMetadataTest
Driver.connect : Connecting with URL: jdbc:postgresql://localhost/SpatialMetadataTest?currentSchema=features
```

---

From: "desruisseaux (@desruisseaux)" <[email-masked]>
Date: Tue, 24 Jun 2025 13:52:34 +0000

Okay, exploring more the differences between the two URLs, the issue seems to be `?currentSchema=features`. Testing `DriverManager` with the URL built by `PGSimpleDataSource`, it works without `?currentSchema=features` but doesn't work if that fragment is present in the URL.

Testing on the command-line with `psql`, the `?currentSchema=features` fragment does not seem to be accepted at all. I do not see that parameter in the page [referenced from here](https://www.prisma.io/dataguide/postgresql/short-guides/connection-uris#specifying-additional-parameters). Is it still supported?

If not, should `PGSimpleDataSource.setCurrentSchema(String)` be deprecated?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 25 Jun 2025 11:20:15 +0000

> Okay, exploring more the differences between the two URLs, the issue seems to be `?currentSchema=features`. Testing `DriverManager` with the URL built by `PGSimpleDataSource`, it works without `?cu…

currentSchema=xyz simply changes the search path upon startup.
> Testing on the command-line with `psql`, the `?currentSchema=features` fragment does not seem to be accepted at all. I do not see that parameter in the page [referenced from here](https://www.prism…

You are conflating psql and pgjdbc. They are not the same
> If not, should `PGSimpleDataSource.setCurrentSchema(String)` be deprecated?
No it should not it is a pgjdbc option

---

From: "desruisseaux (@desruisseaux)" <[email-masked]>
Date: Wed, 25 Jun 2025 11:34:43 +0000

> You are conflating psql and pgjdbc. They are not the same

I know that they are not the same. My argument is that:

1. The addition of a `?currentSchema=features` option in the URL is the only significant difference I could see in the comparison of the `pgjdbc` logs of the two tests posted in [above comment](https://github.com/pgjdbc/pgjdbc/issues/3685#issuecomment-3000511929).
2. That `?currentSchema=features` option is not accepted by `psql`. Error message is: _psql: erreur : paramètre de la requête URI invalide : « currentSchema »_.
3. I did not found `currentSchema` in the list of parameters accepted in the `?foo=bar` part of an URI according [PostgreSQL documentation](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS), contrarily to other parameters such as `?user` and `connect_timeout`.
4. We do observe that when the `?currentSchema` parameter is specified, PostgreSQL 16 or PostGIS do not behave correctly.
5. Therefore, I suspect that `?currentSchema` is not officially supported. Maybe it is a deprecated parameter.

Unless one of the above points is wrong, I thing that `PGSimpleDataSource.setCurrentSchema` should be deprecated.