Message-ID: <gh-pgjdbc-pgjdbc-3685@github.com>
From: "desruisseaux (@desruisseaux)" <noreply+desruisseaux@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Tue, 24 Jun 2025 11:33:03 +0000
Subject: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 1324414
X-GitHub-Author-Login: desruisseaux
X-GitHub-Issue: 3685
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3685
Content-Type: text/plain; charset=utf-8

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)
```