Skip to content

Closing Connection Does Not Close Statement And ResultSet #1903

@danuvian

Description

@danuvian

Describe the issue

(1) Closing the connection object does not close the statement and resultset objects, which it should according to what I read online about JDBC.

(2) Closing the statement object does not close the resultset object.

I tried a different database and driver (MySQL) and verified that the MySQL JDBC driver DOES close these two objects once the connection is closed.

Driver Version?

42.2.16

Java Version?

openjdk version "11.0.7" 2020-04-14

OS Version?

MacOS Catalina - 10.15.6

PostgreSQL Version?

Docker image - "postgres:13"

To Reproduce
Steps to reproduce the behaviour:

public static void main(String[] args) throws Exception {
        String[] dbInfo = {"jdbc:postgresql:my_database", "postgres", "fakepassword"};
        if(args != null && ArrayUtils.contains(args, "mysql"))  {
            dbInfo = new String[]{"jdbc:mysql://localhost:3306/my_database", "mysql", "fakepassword"};
        }
        System.out.println("Trying to connect to: " + dbInfo[0]);

        Connection con = DriverManager.getConnection(dbInfo[0], dbInfo[1], dbInfo[2]);
        PreparedStatement ps = con.prepareStatement("select * from courses"); 
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id") + ", code: " + rs.getString("code") + ", description: "
                    + rs.getString("description"));
        }

        con.close();
        // postresql is not closing resultset or preparedstatement upon con closing!!!
        // postresql:   con closed? true, ps closed? false, rs closed? false
        // mysql:       con closed? true, ps closed? true, rs closed? true
        System.out.println("con closed? " + con.isClosed() + ", ps closed? " + ps.isClosed() 
            + ", rs closed? " + rs.isClosed());
    }

Expected behaviour
A clear and concise description of what you expected to happen.
And what actually happens

Closing connection object closes the statement and resultset objects but what actually happens is that the statement and resultset object are still open (isClosed() returns false).

Closing the statement object closes the resultset object but what actually happens is that the resultset object is still open (isClosed() returns false).

Logs
If possible PostgreSQL logs surrounding the occurrence of the issue
Additionally logs from the driver can be obtained adding

loggerLevel=TRACE&loggerFile=pgjdbc-trace.log 

to the connection string
Using the following template code make sure the bug can be replicated in the driver alone.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class TestNullsFirst {
    public static void main(String []args) throws Exception {


        String url = "jdbc:postgresql://localhost:5432/test";

        Properties props = new Properties();
        props.setProperty("user", "test");
        props.setProperty("password", "test");
        try ( Connection conn = DriverManager.getConnection(url, props) ){
            try ( Statement statement = conn.createStatement() ) {
                try (ResultSet rs = statement.executeQuery( "select lastname from users order by lastname asc nulls first") ){
                    if (rs.next())
                        System.out.println( "Get String: " + rs.getString(1));
                }
            }
        }
    }
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions