pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: desruisseaux (@desruisseaux) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
Date: Tue, 24 Jun 2025 11:33:03 +0000
Message-ID: <[email protected]> (raw)

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

view thread (12+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  Subject: Re: [pgjdbc/pgjdbc] issue #3685: Regression: a query does not work anymore on connection created by `PGSimpleDataSource` but still work with `DriverManager`
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox