pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3889: JDBC Not Sending CLOSE packet
4+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3889: JDBC Not Sending CLOSE packet
@ 2025-12-07 14:18 "manav-yb (@manav-yb)" <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: manav-yb (@manav-yb) @ 2025-12-07 14:18 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Please read https://stackoverflow.com/help/minimal-reproducible-example
**Describe the issue**
Wrote an JDBC application which uses extended protocol level prepared statements. And using pstmt.close() to deallocate the same prep stmt. But in my server logs I don't see CLOSE packet been sent by driver.
**Driver Version?**
postgresql-42.7.1.jar
**Java Version?**
javac 1.8.0_29
**OS Version?**
**PostgreSQL Version?**
PG-15.
**To Reproduce**
Create a table in any database/user.
CREATE TABLE T_(A INT, B INT);
Run the below application.
**Expected behaviour**
Expectation is i should get CLOSE packet and prepared statement (`S_1 insert into t_ values ($1, $2)`) should not comes in pg_prepared_statements table after calling pstmt1.close.
What i see is that: prepared statement (S_1 insert into t_ values ($1, $2)) comes even after calling pstmt1.close().
**Logs**
If possible PostgreSQL logs surrounding the occurrence of the issue
Additionally logs from the driver can be obtained adding
Using the following template code make sure the bug can be replicated in the driver alone.
```
import java.sql.*;
public class sendClose3 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pstmt1 = null, pstmt2 = null;
try {
// prepareThreshold=1 is crucial here
connection = DriverManager.getConnection("jdbc:postgresql://10.150.3.175:5433/yugabyte?prepareThreshold=1", "yugabyte","yugabyte");
// ---------------------------------------------------------
// CHANGE 1: Use placeholders (?)
// This forces the driver to use Extended Query Protocol (Parse/Bind)
// and create a named statement (e.g., "S_1") on the server.
// ---------------------------------------------------------
pstmt1 = connection.prepareStatement("insert into t_ values (?, ?)");
pstmt1.setInt(1, 1);
pstmt1.setInt(2, 2);
// Execute twice to ensure the driver switches to server-prepare mode
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
pstmt1.execute();
// ---------------------------------------------------------
// CHANGE 2: Close the statement
// The driver now marks "S_1" as closed internally and queues the packet.
// ---------------------------------------------------------
Statement stmt = connection.createStatement();
System.out.println("Before closing prepared statement");
ResultSet rs = stmt.executeQuery("select name, statement from pg_prepared_statements");
while (rs.next()) {
System.out.println(rs.getString("name") + " " + rs.getString("statement"));
}
rs.close();
// stmt.close();
pstmt1.close();
System.out.println("After closing prepared statement");
rs = stmt.executeQuery("select name, statement from pg_prepared_statements");
while (rs.next()) {
System.out.println(rs.getString("name") + " " + rs.getString("statement"));
}
rs.close();
stmt.close();
connection.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
```
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3889: JDBC Not Sending CLOSE packet
@ 2025-12-07 14:37 ` "vlsi (@vlsi)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: vlsi (@vlsi) @ 2025-12-07 14:37 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
pgjdbc has a cache of statements, so it is able to reuse sever-side statements even in the case of `stmt.close`.
See https://stackoverflow.com/questions/32297503/whats-the-life-span-of-a-postgresql-server-side-prepare...
Why do you expect `CLOSE` message?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3889: JDBC Not Sending CLOSE packet
@ 2025-12-07 15:16 ` "manav-yb (@manav-yb)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: manav-yb (@manav-yb) @ 2025-12-07 15:16 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Expecting CLOSE to validate pstmt.close() is sent.
But from server point of view, not sending actual CLOSE message, I see 2 problems and solutions as well, please correct me if I'm wrong.
1) There can be a resource leak if lot of prepared statements unused been prepared on a long lived backend. But JDBC is configuring it with help of preparedStatementCacheQueries, developer can set it to 0 but he/she won't get advantage of shared prepared statements.
2) On schema change it can cause an Error - "Cached plan must not change result type". Because CLOSE has not been explicitly send by driver even application has written pstmt.close(). But given JDBC has a the mechansim to retry (when it receives such error) it silently by sending actual CLOSE message followed by new PARSE packet.
Are there are any other known problems which has or has not workaround.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3889: JDBC Not Sending CLOSE packet
@ 2025-12-07 15:23 ` "vlsi (@vlsi)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: vlsi (@vlsi) @ 2025-12-07 15:23 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> There can be a resource leak if lot of prepared statements unused been prepared on a long lived backend
The statement cache has limited size per connection, so it is not a memory leak.
> On schema change it can cause an Error - "Cached plan must not change result type".
That is out of control for the jdbc driver. Please ask PostgreSQL database developers to fix the issue.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-12-07 15:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-07 14:18 [pgjdbc/pgjdbc] issue #3889: JDBC Not Sending CLOSE packet "manav-yb (@manav-yb)" <[email protected]>
2025-12-07 14:37 ` "vlsi (@vlsi)" <[email protected]>
2025-12-07 15:16 ` "manav-yb (@manav-yb)" <[email protected]>
2025-12-07 15:23 ` "vlsi (@vlsi)" <[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