pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
12+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 11:33 "desruisseaux (@desruisseaux)" <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: desruisseaux (@desruisseaux) @ 2025-06-24 11:33 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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)
```
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 11:36 ` "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2025-06-24 11:36 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I'm not sure how this is a pgjdbc problem if it works in version 15?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 12:09 ` "desruisseaux (@desruisseaux)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: desruisseaux (@desruisseaux) @ 2025-06-24 12:09 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Because it works with `DriverManager` but not with `PGSimpleDataSource`. So it seems to be a mix of pgjdbc and PostgreSQL / PostGIS issues.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 12:13 ` "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2025-06-24 12:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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 ?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 12:31 ` "desruisseaux (@desruisseaux)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: desruisseaux (@desruisseaux) @ 2025-06-24 12:31 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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')
```
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 12:36 ` "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2025-06-24 12:36 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Well, without a way to replicate it I really can't help.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 12:39 ` "desruisseaux (@desruisseaux)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: desruisseaux (@desruisseaux) @ 2025-06-24 12:39 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
The test case in the summary of this issue replicate it.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 12:41 ` "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2025-06-24 12:41 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 13:32 ` "desruisseaux (@desruisseaux)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: desruisseaux (@desruisseaux) @ 2025-06-24 13:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
```
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-24 13:52 ` "desruisseaux (@desruisseaux)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: desruisseaux (@desruisseaux) @ 2025-06-24 13:52 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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-parame...). Is it still supported?
If not, should `PGSimpleDataSource.setCurrentSchema(String)` be deprecated?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-25 11:20 ` "davecramer (@davecramer)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: davecramer (@davecramer) @ 2025-06-25 11:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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.
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.prisma.io/dataguide/postgresql/short-guides/connection-uris#specifying-additional-parame...). Is it still supported?
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
@ 2025-06-25 11:34 ` "desruisseaux (@desruisseaux)" <[email protected]>
10 siblings, 0 replies; 12+ messages in thread
From: desruisseaux (@desruisseaux) @ 2025-06-25 11:34 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-06-25 11:34 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-24 11:33 [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager` "desruisseaux (@desruisseaux)" <[email protected]>
2025-06-24 11:36 ` "davecramer (@davecramer)" <[email protected]>
2025-06-24 12:09 ` "desruisseaux (@desruisseaux)" <[email protected]>
2025-06-24 12:13 ` "davecramer (@davecramer)" <[email protected]>
2025-06-24 12:31 ` "desruisseaux (@desruisseaux)" <[email protected]>
2025-06-24 12:36 ` "davecramer (@davecramer)" <[email protected]>
2025-06-24 12:39 ` "desruisseaux (@desruisseaux)" <[email protected]>
2025-06-24 12:41 ` "davecramer (@davecramer)" <[email protected]>
2025-06-24 13:32 ` "desruisseaux (@desruisseaux)" <[email protected]>
2025-06-24 13:52 ` "desruisseaux (@desruisseaux)" <[email protected]>
2025-06-25 11:20 ` "davecramer (@davecramer)" <[email protected]>
2025-06-25 11:34 ` "desruisseaux (@desruisseaux)" <[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