Message-ID: <gh-pgjdbc-pgjdbc-3685-c3000511929@github.com>
From: "desruisseaux (@desruisseaux)" <noreply+desruisseaux@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Tue, 24 Jun 2025 13:32:01 +0000
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: <gh-pgjdbc-pgjdbc-3685@github.com>
References: <gh-pgjdbc-pgjdbc-3685@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: desruisseaux
X-GitHub-Comment-Id: 3000511929
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 3685
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3685#issuecomment-3000511929
Content-Type: text/plain; charset=utf-8

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