Postgres Pro
Downloads
Home   >   mailing lists

Thread: [JDBC] Statement is still active at the back-end even after closing

[JDBC] Statement is still active at the back-end even after closing

From
Syam Pillai
Date:
​Not sure if this was reported earlier.

I am using PostgreSQL 9.5.8 and JDBC driver 42.1.4

If I execute the following statements and at Position #1, if I check the pg_stat_activity from another connection (let's say from the psql application), I can see that the 'Statement s' is still active:

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cadup","user", "password");
Statement s = connection.createStatement();
ResultSet rs =s.executeQuery("SELECT 1 FROM core.Person");
if(rs.next()) {
System.err.println("Yes, we got a row!");
} else {
System.err.println("No rows found");
}
s.close();
Thread.sleep(10000); // Position #1
connection.close();
} catch(Exception e) {
e.printStackTrace();
}


===============
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person

Re: [JDBC] Statement is still active at the back-end even after closing

From
Dave Cramer
Date:
Seems pretty normal to me ? You have a sleep in there which is keeping it alive. what happens if you check it after the close ?


On 31 August 2017 at 04:50, Syam Pillai <syam@engravgroup.com> wrote:
​Not sure if this was reported earlier.

I am using PostgreSQL 9.5.8 and JDBC driver 42.1.4

If I execute the following statements and at Position #1, if I check the pg_stat_activity from another connection (let's say from the psql application), I can see that the 'Statement s' is still active:

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cadup","user", "password");
Statement s = connection.createStatement();
ResultSet rs =s.executeQuery("SELECT 1 FROM core.Person");
if(rs.next()) {
System.err.println("Yes, we got a row!");
} else {
System.err.println("No rows found");
}
s.close();
Thread.sleep(10000); // Position #1
connection.close();
} catch(Exception e) {
e.printStackTrace();
}


===============
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person


Re: Statement is still active at the back-end even after closing

From
Dave Cramer
Date:
Seems pretty normal to me ? You have a sleep in there which is keeping it alive. what happens if you check it after the close ?


On 31 August 2017 at 04:50, Syam Pillai <syam@engravgroup.com> wrote:
​Not sure if this was reported earlier.

I am using PostgreSQL 9.5.8 and JDBC driver 42.1.4

If I execute the following statements and at Position #1, if I check the pg_stat_activity from another connection (let's say from the psql application), I can see that the 'Statement s' is still active:

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cadup","user", "password");
Statement s = connection.createStatement();
ResultSet rs =s.executeQuery("SELECT 1 FROM core.Person");
if(rs.next()) {
System.err.println("Yes, we got a row!");
} else {
System.err.println("No rows found");
}
s.close();
Thread.sleep(10000); // Position #1
connection.close();
} catch(Exception e) {
e.printStackTrace();
}


===============
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person


Re: [JDBC] Statement is still active at the back-end even after closing

From
Syam Pillai
Date:
Just before the sleep, I already closed the Statement. Connection is not closed because I want to use the connection for executing further Statements.

On Thu, Aug 31, 2017 at 5:09 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Seems pretty normal to me ? You have a sleep in there which is keeping it alive. what happens if you check it after the close ?


On 31 August 2017 at 04:50, Syam Pillai <syam@engravgroup.com> wrote:
​Not sure if this was reported earlier.

I am using PostgreSQL 9.5.8 and JDBC driver 42.1.4

If I execute the following statements and at Position #1, if I check the pg_stat_activity from another connection (let's say from the psql application), I can see that the 'Statement s' is still active:

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cadup","user", "password");
Statement s = connection.createStatement();
ResultSet rs =s.executeQuery("SELECT 1 FROM core.Person");
if(rs.next()) {
System.err.println("Yes, we got a row!");
} else {
System.err.println("No rows found");
}
s.close();
Thread.sleep(10000); // Position #1
connection.close();
} catch(Exception e) {
e.printStackTrace();
}


===============
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person





--
Syam S. Pillai, Director & Chief Technology Officer
ENGRAV Aviation Services & Systems Pvt. Ltd.
# 15, Level 1, Indradhanush,  Gubbi Cross,
Kothannur PO, Bangalore - 560 077, India.
Phone: +91 80 2844 3740

Re: Statement is still active at the back-end even after closing

From
Syam Pillai
Date:
Just before the sleep, I already closed the Statement. Connection is not closed because I want to use the connection for executing further Statements.

On Thu, Aug 31, 2017 at 5:09 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Seems pretty normal to me ? You have a sleep in there which is keeping it alive. what happens if you check it after the close ?


On 31 August 2017 at 04:50, Syam Pillai <syam@engravgroup.com> wrote:
​Not sure if this was reported earlier.

I am using PostgreSQL 9.5.8 and JDBC driver 42.1.4

If I execute the following statements and at Position #1, if I check the pg_stat_activity from another connection (let's say from the psql application), I can see that the 'Statement s' is still active:

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cadup","user", "password");
Statement s = connection.createStatement();
ResultSet rs =s.executeQuery("SELECT 1 FROM core.Person");
if(rs.next()) {
System.err.println("Yes, we got a row!");
} else {
System.err.println("No rows found");
}
s.close();
Thread.sleep(10000); // Position #1
connection.close();
} catch(Exception e) {
e.printStackTrace();
}


===============
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person





--
Syam S. Pillai, Director & Chief Technology Officer
ENGRAV Aviation Services & Systems Pvt. Ltd.
# 15, Level 1, Indradhanush,  Gubbi Cross,
Kothannur PO, Bangalore - 560 077, India.
Phone: +91 80 2844 3740

Re: Statement is still active at the back-end even after closing

From
Dave Cramer
Date:
So this is perfectly normal. Closing the statement only closes resources on the driver side there is no interaction with the server.


On 31 August 2017 at 12:05, Syam Pillai <syam@engravgroup.com> wrote:
Just before the sleep, I already closed the Statement. Connection is not closed because I want to use the connection for executing further Statements.

On Thu, Aug 31, 2017 at 5:09 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Seems pretty normal to me ? You have a sleep in there which is keeping it alive. what happens if you check it after the close ?


On 31 August 2017 at 04:50, Syam Pillai <syam@engravgroup.com> wrote:
​Not sure if this was reported earlier.

I am using PostgreSQL 9.5.8 and JDBC driver 42.1.4

If I execute the following statements and at Position #1, if I check the pg_stat_activity from another connection (let's say from the psql application), I can see that the 'Statement s' is still active:

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cadup","user", "password");
Statement s = connection.createStatement();
ResultSet rs =s.executeQuery("SELECT 1 FROM core.Person");
if(rs.next()) {
System.err.println("Yes, we got a row!");
} else {
System.err.println("No rows found");
}
s.close();
Thread.sleep(10000); // Position #1
connection.close();
} catch(Exception e) {
e.printStackTrace();
}


===============
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person





--
Syam S. Pillai, Director & Chief Technology Officer
ENGRAV Aviation Services & Systems Pvt. Ltd.
# 15, Level 1, Indradhanush,  Gubbi Cross,
Kothannur PO, Bangalore - 560 077, India.


Re: [JDBC] Statement is still active at the back-end even after closing

From
Dave Cramer
Date:
So this is perfectly normal. Closing the statement only closes resources on the driver side there is no interaction with the server.


On 31 August 2017 at 12:05, Syam Pillai <syam@engravgroup.com> wrote:
Just before the sleep, I already closed the Statement. Connection is not closed because I want to use the connection for executing further Statements.

On Thu, Aug 31, 2017 at 5:09 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Seems pretty normal to me ? You have a sleep in there which is keeping it alive. what happens if you check it after the close ?


On 31 August 2017 at 04:50, Syam Pillai <syam@engravgroup.com> wrote:
​Not sure if this was reported earlier.

I am using PostgreSQL 9.5.8 and JDBC driver 42.1.4

If I execute the following statements and at Position #1, if I check the pg_stat_activity from another connection (let's say from the psql application), I can see that the 'Statement s' is still active:

try {
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cadup","user", "password");
Statement s = connection.createStatement();
ResultSet rs =s.executeQuery("SELECT 1 FROM core.Person");
if(rs.next()) {
System.err.println("Yes, we got a row!");
} else {
System.err.println("No rows found");
}
s.close();
Thread.sleep(10000); // Position #1
connection.close();
} catch(Exception e) {
e.printStackTrace();
}


===============
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person





--
Syam S. Pillai, Director & Chief Technology Officer
ENGRAV Aviation Services & Systems Pvt. Ltd.
# 15, Level 1, Indradhanush,  Gubbi Cross,
Kothannur PO, Bangalore - 560 077, India.


Re: [JDBC] Statement is still active at the back-end even after closing

From
"David G. Johnston"
Date:
On Thu, Aug 31, 2017 at 1:50 AM, Syam Pillai <syam@engravgroup.com> wrote:
​
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person


​state = "idle" which means that they query column is showing the last executed query - not something that is presently running.

David J.
​

Re: Statement is still active at the back-end even after closing

From
"David G. Johnston"
Date:
On Thu, Aug 31, 2017 at 1:50 AM, Syam Pillai <syam@engravgroup.com> wrote:
​
Query executed to check the activity:
SELECT pid, datname, now() - pg_stat_activity.query_start AS duration, state, query
FROM pg_stat_activity
WHERE now() - pg_stat_activity.query_start > interval '1 second';
Output from psql:
  pid  | datname |    duration     | state |           query           
-------+---------+-----------------+-------+---------------------------
 17775 | xxxx   | 00:00:07.481294 | idle  | SELECT 1 FROM core.Person


​state = "idle" which means that they query column is showing the last executed query - not something that is presently running.

David J.
​