statement stays in "D" STATUS after "kill REQUEST_ID"

[Alvin Peng]

My test is run in windows, with 2 nodes in a postgresql database.

The client program runs insert/select/delete/update by statement and preparedStatement.
Multiple clients are started.

Some client happens to stop in the "update ..." SQL.
I try to kill the SQL via "kill 587", also I kill the client.

With show statements command, I still get below info:

Stado -> show statements;
+--------------------------------------------------------------------------------------------------------------------------------+
| REQUEST_ID | SESSION_ID | SUBMIT_TIME | STATUS | STATEMENT | NODES | CURRENT_STEP |
+--------------------------------------------------------------------------------------------------------------------------------+
| 587 | 14 | 2013-06-19 10:46:55.671 | D | update info set name = '123' where id = $1 | | |
| 2001 | 19 | 2013-06-19 11:38:36.312 | E | show statements | | |
+--------------------------------------------------------------------------------------------------------------------------------+
2 row(s).

1. If I kill gs-cmdline, seems server will know the client connection is closed, but when I kill my client program, seems server doesn't know?
2. Why the killed statement still exists?

[Alvin Peng (pengalvin)]

provide more information:

run test function, which is simplify as below:

    private static void test() throws Exception{
  String sql = "select * from info a, info b where a.id = ? and a.id = b.id";

  pstmt = conn.prepareStatement(sql);

  for(int idx=1; idx<TIMES; idx++){
   pstmt.setInt(1, idx);
   pstmt.executeQuery();
  }

  pstmt.close();
 }

1) with xdb.use_load_for_step=false config, the test runs with no problem.
2) with default config xdb.use_load_for_step=true, there are some problems:
     1. sometimes the program stops after serval queries, it does not exit, there is no exception either.

Stado -> show statements;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| REQUEST_ID | SESSION_ID | SUBMIT_TIME | STATUS | STATEMENT | NODES | CURRENT_STEP |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| 17 | 6 | 2013-06-24 18:40:19.03 | E | show statements | | |
| 15 | 7 | 2013-06-24 18:38:45.071 | E | select * from info a, info b where a.id = $1 and a.id = b.id | | |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 row(s).

if kill the request:

Stado -> kill 15;
OK

Stado -> show statements;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| REQUEST_ID | SESSION_ID | SUBMIT_TIME | STATUS | STATEMENT | NODES | CURRENT_STEP |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| 19 | 6 | 2013-06-24 18:40:23.45 | E | show statements | | |
| 15 | 7 | 2013-06-24 18:38:45.071 | D | select * from info a, info b where a.id = $1 and a.id = b.id | | |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 row(s).

the statement of request 15 stays in "D" status, and is not deleted even the program is terminated.

     2. sometimes there is exception telling "relation TMPT**_1 already exists", and the program stops and exits.

[Alvin Peng (pengalvin)]

Another testing:

private static void testPreStmtComplicatedSelect3() throws Exception{
  String sql = "select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id);";

  pstmt = conn.prepareStatement(sql);

  for(int idx=1; idx<TIMES; idx++){
   pstmt.setInt(1, idx);
   pstmt.executeQuery();
  }

  pstmt.close();
 }

results:

executeQuery: select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id); (1)
executeQuery: select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id); (2)
executeQuery: select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id); (3)
executeQuery: select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id); (4)
executeQuery: select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id); (5)
executeQuery: select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id); (6)
Exception in thread "main" org.postgresql.driver.util.PSQLException: ERROR: relation "TMPTT156_1" already exists
 at org.postgresql.driver.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2155)
 at org.postgresql.driver.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1888)
 at org.postgresql.driver.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:269)
 at org.postgresql.driver.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
 at org.postgresql.driver.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:400)
 at org.postgresql.driver.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285)

There is exception telling "TMPT**" table already exists after 5 queries.

[Mason Sharp (mason-sharp)]

Alvin,

Is this on the sgdg development branch?

We need to rename the these...

Thanks,

Mason

On Tuesday, June 25, 2013, Alvin Peng wrote:

> Another testing:
>
> private static void testPreStmtComplicatedSelect3() throws Exception{
> String sql = "select count(*) from info a where exists
> (select * from info2 b where a.id=? and b.id=a.id);";
>
> pstmt = conn.prepareStatement(sql);
>
> for(int idx=1; idx<TIMES; idx++){
> pstmt.setInt(1, idx);
> pstmt.executeQuery();
> }
>
> pstmt.close();
> }
>
> results:
>
> executeQuery: select count(*) from info a where exists (select * from
> info2 b where a.id=? and b.id=a.id); (1)
> executeQuery: select count(*) from info a where exists (select * from
> info2 b where a.id=? and b.id=a.id); (2)
> executeQuery: select count(*) from info a where exists (select * from
> info2 b where a.id=? and b.id=a.id); (3)
> executeQuery: select count(*) from info a where exists (select * from
> info2 b where a.id=? and b.id=a.id); (4)
> executeQuery: select count(*) from info a where exists (select * from
> info2 b where a.id=? and b.id=a.id); (5)
> executeQuery: select count(*) from info a where exists (select * from
> info2 b where a.id=? and b.id=a.id); (6)
> Exception in thread "main" org.postgresql.driver.util.PSQLException:
> ERROR: relation "TMPTT156_1" already exists
> at
> org.postgresql.driver.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2155)
> at
> org.postgresql.driver.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1888)
> at
> org.postgresql.driver.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:269)
> at
> org.postgresql.driver.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
> at
> org.postgresql.driver.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:400)
> at
> org.postgresql.driver.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285)
>
> There is exception telling "TMPT**" table already exists after 5
> queries.
>
> --
> You received this bug notification because you are a member of Stado
> Global Development Group, which is subscribed to Stado.
> https://bugs.launchpad.net/bugs/1192432
>
> Title:
> statement stays in "D" STATUS after "kill REQUEST_ID"
>
> Status in Stado: The Open Source MPP Solution:
> New
>
> Bug description:
> My test is run in windows, with 2 nodes in a postgresql database.
>
> The client program runs insert/select/delete/update by statement and
> preparedStatement.
> Multiple clients are started.
>
> Some client happens to stop in the "update ..." SQL.
> I try to kill the SQL via "kill 587", also I kill the client.
>
> With show statements command, I still get below info:
>
> Stado -> show statements;
>
> +--------------------------------------------------------------------------------------------------------------------------------+
> | REQUEST_ID | SESSION_ID | SUBMIT_TIME | STATUS |
> STATEMENT | NODES | CURRENT_STEP |...

[Alvin Peng (pengalvin)]

Hi Mason,

Yes, I use revision 107 codes on the sgdg development branch for the testing.

The #Exception in thread "main" org.postgresql.driver.util.PSQLException: ERROR: relation "TMPTT156_1" already exists# issue can be reproduced by below test:

private static void testPreStmtComplicatedSelect3() throws Exception{
  String sql = "select count(*) from info a where exists (select * from info2 b where a.id=? and b.id=a.id);";

  pstmt = conn.prepareStatement(sql);

  for(int idx=1; idx<TIMES; idx++){
   pstmt.setInt(1, idx);
   pstmt.executeQuery();
  }

  pstmt.close();
 }