[jira] [Comment Edited] (HAWQ-1530) Illegally killing a JDBC select query causes locking problems
[ https://issues.apache.org/jira/browse/HAWQ-1530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16237223#comment-16237223 ] Grant Krieger edited comment on HAWQ-1530 at 11/3/17 11:50 AM: --- Hi, I think any JDBC client from windows will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC from windows only. Do not use psql on linux as this seems to work fine --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL (Do not just stop the query) before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a.*, b.description FROM PUBLIC.test_table_fact3 a JOIN PUBLIC.test_prod_dim3 b ON a.productid = b.productid ) AS q LIMIT 1; -- end test query --Then in a new session try drop the table . Unable to do so as its locked and unable to free lock until restart of server drop table public.test_prod_dim3; select * from pg_stat_activity where procpid = 335492 1075919 edw_performance3 335492 342 10gpadmin drop table public.test_prod_dim3 true 2017/09/21 10:51:22 AM 2017/09/21 8:58:55 AM 10.1.3.201 61257 2017/09/21 10:51:22 AM false select * from pg_locks where pid = 335492 relation1075919 1710089 (null) (null) (null) (null) (null) (null) 109830156 335492 AccessExclusiveLock false 342 false -1 transactionid (null) (null) (null) (null) 109830156 (null) (null) (null) 109830156 335492 ExclusiveLock true 342 false -1 relation1075919 1259 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 2608 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 1247 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 Thank you was (Author: ncsballie): Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL (Do not just stop the query) before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT
[jira] [Comment Edited] (HAWQ-1530) Illegally killing a JDBC select query causes locking problems
[ https://issues.apache.org/jira/browse/HAWQ-1530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16237223#comment-16237223 ] Grant Krieger edited comment on HAWQ-1530 at 11/3/17 7:51 AM: -- Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL (Do not just stop the query) before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a.*, b.description FROM PUBLIC.test_table_fact3 a JOIN PUBLIC.test_prod_dim3 b ON a.productid = b.productid ) AS q LIMIT 1; -- end test query --Then in a new session try drop the table . Unable to do so as its locked and unable to free lock until restart of server drop table public.test_prod_dim3; select * from pg_stat_activity where procpid = 335492 1075919 edw_performance3 335492 342 10gpadmin drop table public.test_prod_dim3 true 2017/09/21 10:51:22 AM 2017/09/21 8:58:55 AM 10.1.3.201 61257 2017/09/21 10:51:22 AM false select * from pg_locks where pid = 335492 relation1075919 1710089 (null) (null) (null) (null) (null) (null) 109830156 335492 AccessExclusiveLock false 342 false -1 transactionid (null) (null) (null) (null) 109830156 (null) (null) (null) 109830156 335492 ExclusiveLock true 342 false -1 relation1075919 1259 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 2608 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 1247 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 Thank you was (Author: ncsballie): Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL (Do not just stop the query) before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT
[jira] [Comment Edited] (HAWQ-1530) Illegally killing a JDBC select query causes locking problems
[ https://issues.apache.org/jira/browse/HAWQ-1530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16237223#comment-16237223 ] Grant Krieger edited comment on HAWQ-1530 at 11/3/17 7:50 AM: -- Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL (Do not just stop the query) before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a.*, b.description FROM PUBLIC.test_table_fact3 a JOIN PUBLIC.test_prod_dim3 b ON a.productid = b.productid ) AS q LIMIT 1; -- end test query --Then in a new session try drop the table . Unable to do so as its locked and unable to free lock until restart of server drop table public.test_prod_dim3; select * from pg_stat_activity where procpid = 335492 1075919 edw_performance3 335492 342 10gpadmin drop table public.test_prod_dim3 true 2017/09/21 10:51:22 AM 2017/09/21 8:58:55 AM 10.1.3.201 61257 2017/09/21 10:51:22 AM false select * from pg_locks where pid = 335492 relation1075919 1710089 (null) (null) (null) (null) (null) (null) 109830156 335492 AccessExclusiveLock false 342 false -1 transactionid (null) (null) (null) (null) 109830156 (null) (null) (null) 109830156 335492 ExclusiveLock true 342 false -1 relation1075919 1259 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 2608 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 1247 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 was (Author: ncsballie): Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL (Do not just stop the query) before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a
[jira] [Comment Edited] (HAWQ-1530) Illegally killing a JDBC select query causes locking problems
[ https://issues.apache.org/jira/browse/HAWQ-1530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16237223#comment-16237223 ] Grant Krieger edited comment on HAWQ-1530 at 11/3/17 7:47 AM: -- Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL (Do not just stop the query) before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a.*, b.description FROM PUBLIC.test_table_fact3 a JOIN PUBLIC.test_prod_dim3 b ON a.productid = b.productid ) AS q LIMIT 1; -- end test query --Then in a new session drop table public.test_prod_dim3; select * from pg_stat_activity where procpid = 335492 1075919 edw_performance3 335492 342 10gpadmin drop table public.test_prod_dim3 true 2017/09/21 10:51:22 AM 2017/09/21 8:58:55 AM 10.1.3.201 61257 2017/09/21 10:51:22 AM false select * from pg_locks where pid = 335492 relation1075919 1710089 (null) (null) (null) (null) (null) (null) 109830156 335492 AccessExclusiveLock false 342 false -1 transactionid (null) (null) (null) (null) 109830156 (null) (null) (null) 109830156 335492 ExclusiveLock true 342 false -1 relation1075919 1259 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 2608 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 1247 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 was (Author: ncsballie): Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a.*, b.description FROM PUBLIC.test_table_fact3 a JOIN PUBLIC.tes
[jira] [Comment Edited] (HAWQ-1530) Illegally killing a JDBC select query causes locking problems
[ https://issues.apache.org/jira/browse/HAWQ-1530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16237223#comment-16237223 ] Grant Krieger edited comment on HAWQ-1530 at 11/3/17 7:46 AM: -- Hi, I think any JDBC client will do. Aqua should not be a must Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a.*, b.description FROM PUBLIC.test_table_fact3 a JOIN PUBLIC.test_prod_dim3 b ON a.productid = b.productid ) AS q LIMIT 1; -- end test query --Then in a new session drop table public.test_prod_dim3; select * from pg_stat_activity where procpid = 335492 1075919 edw_performance3 335492 342 10gpadmin drop table public.test_prod_dim3 true 2017/09/21 10:51:22 AM 2017/09/21 8:58:55 AM 10.1.3.201 61257 2017/09/21 10:51:22 AM false select * from pg_locks where pid = 335492 relation1075919 1710089 (null) (null) (null) (null) (null) (null) 109830156 335492 AccessExclusiveLock false 342 false -1 transactionid (null) (null) (null) (null) 109830156 (null) (null) (null) 109830156 335492 ExclusiveLock true 342 false -1 relation1075919 1259 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 2608 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 relation1075919 1247 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -1 was (Author: ncsballie): Hi, Below is a test script which loads data then a test query. Please do this through JDBC only. Do not use psql. Problem is from JDBC only. --- loading dummy data DROP TABLE if EXISTS PUBLIC.test_prod_dim3; CREATE TABLE PUBLIC.test_prod_dim3 ( productid int4 NULL, description text NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_prod_dim3 SELECT 1 AS productid, '1 desc' AS description FROM generate_series(1,1); DROP TABLE if EXISTS PUBLIC.test_table_fact3; CREATE TABLE PUBLIC.test_table_fact3 ( productid int4 NULL, value1 int4 NULL ) WITH ( APPENDONLY = TRUE, COMPRESSTYPE = SNAPPY, OIDS = FALSE ) DISTRIBUTED RANDOMLY; INSERT INTO PUBLIC.test_table_fact3 SELECT 1 AS productid, 1 AS value1 FROM generate_series(1,1); --End loading dummy data --Starting test query and kill it with CTRL ALT DEL before it finishes . (Test and kill from windows only) . You will notice query kills but lock on table is never released until reboot of server or kill -9 -- test query (This should be killed before completion) SELECT * FROM ( SELECT a.*, b.description FROM PUBLIC.test_table_fact3 a JOIN PUBLIC.test_prod_dim3 b ON a.productid = b.productid ) AS q LIMIT 1; -- end te
