[ https://issues.apache.org/jira/browse/HAWQ-1530?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16237223#comment-16237223 ]
Grant Krieger commented on HAWQ-1530: ------------------------------------- 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,100000000); 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,100000000); --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 10000; -- 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 10 gpadmin 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 relation 1075919 1710089 (null) (null) (null) (null) (null) (null) 109830156 335492 AccessExclusiveLock false 342 false -10000 transactionid (null) (null) (null) (null) 109830156 (null) (null) (null) 109830156 335492 ExclusiveLock true 342 false -10000 relation 1075919 1259 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -10000 relation 1075919 2608 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -10000 relation 1075919 1247 (null) (null) (null) (null) (null) (null) 109830156 335492 RowExclusiveLock true 342 false -10000 > Illegally killing a JDBC select query causes locking problems > ------------------------------------------------------------- > > Key: HAWQ-1530 > URL: https://issues.apache.org/jira/browse/HAWQ-1530 > Project: Apache HAWQ > Issue Type: Bug > Components: Transaction > Reporter: Grant Krieger > Assignee: Radar Lei > Priority: Major > > Hi, > When you perform a long running select statement on 2 hawq tables (join) from > JDBC and illegally kill the JDBC client (CTRL ALT DEL) before completion of > the query the 2 tables remained locked even when the query completes on the > server. > The lock is visible via PG_locks. One cannot kill the query via SELECT > pg_terminate_backend(393937). The only way to get rid of it is to kill -9 > from linux or restart hawq but this can kill other things as well. > The JDBC client I am using is Aqua Data Studio. > I can provide exact steps to reproduce if required > Thank you > Grant -- This message was sent by Atlassian JIRA (v6.4.14#64029)