[jira] [Commented] (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=16394885#comment-16394885 ] Shubham Sharma commented on HAWQ-1530: -- This issue is reproducible with 2.3.0.0 when multiple connections are opened using pgAdmin3(JDBC client). Also, another important part of reproducing this issue is that the communication between pgAdmin3 and hawq must be over network. If hawq and pgAdmin3 are present on same host the issue will not reproduce. > 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: Yi Jin >Priority: Major > Fix For: 2.3.0.0-incubating > > > 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 (v7.6.3#76005)
[jira] [Commented] (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=16344186#comment-16344186 ] Yi Jin commented on HAWQ-1530: -- Close this issue as probably the fix worked for this problem. > 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: Yi Jin >Priority: Major > Fix For: 2.3.0.0-incubating > > > 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 (v7.6.3#76005)
[jira] [Commented] (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=16245198#comment-16245198 ] Grant Krieger commented on HAWQ-1530: - Hi Yi, Thank you for the explanation. Regards Grant > 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: Yi Jin > Fix For: 2.3.0.0-incubating > > > 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)
[jira] [Commented] (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=16244963#comment-16244963 ] Yi Jin commented on HAWQ-1530: -- I pushed the fix, Grant. Thank you for your help. I think you can try to randomly cancel or terminate one running query/insert anytime, and then check the consequent DROP TABLE works without hang. This is not so easy to test, the reason I can explain here more to let you know what happens before fixing: A process accesses table T with shared lock, and FATAL error is raised when it is cancelled or terminated, unfortunately, when another ERROR occurs and it is promoted to a FATAL error, this breaks normal transaction cleanup logic, this causes the lock counter structure not correctly cleaned. Then when B process access table T2, B reuses A used lock counter structure, and after all B logic and computation, B has to clean up the lock counter structure again, because this has been polluted by A, B cannot make counter back to 0 again, which makes B not able to release lock of T2. B happily exits then without reporting any error. This is reason why we can observe one lock occupied by unknown process. When a bad luck process C comes and tries to DROP TABLE, it cannot get exclusive lock. So C hangs. The fix is to drop potential ERROR when HAWQ is in exit progress due to previous FATAL error. > 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: Yi Jin > Fix For: 2.3.0.0-incubating > > > 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)
[jira] [Commented] (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=16243416#comment-16243416 ] Grant Krieger commented on HAWQ-1530: - Thanks for the help. If there is anything I can assist with from a testing perspective please advise > 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: Yi Jin > Fix For: 2.3.0.0-incubating > > > 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)
[jira] [Commented] (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=16242854#comment-16242854 ] Yi Jin commented on HAWQ-1530: -- I created one PR for this bug, https://github.com/apache/incubator-hawq/pull/1308 Please have a check > 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: Yi Jin > Fix For: 2.3.0.0-incubating > > > 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)
[jira] [Commented] (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=16241780#comment-16241780 ] Yi Jin commented on HAWQ-1530: -- I will provide a fix very soon which possibly fix this issue . > 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 > > 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)
[jira] [Commented] (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=16241629#comment-16241629 ] Lei Chang commented on HAWQ-1530: - Thanks [~kuien] . I think [~yjin] fixed a bug before, it is quite similar to this bug. > 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 > > 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)
[jira] [Commented] (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=16241605#comment-16241605 ] Kuien Liu commented on HAWQ-1530: - My partner, Zhiyong Dai, has prepared a case to reproduce this issue: 1. Submit "SELECT * FROM tbl" through JDBC client; 2. Send SIGTSTP (by ctrl+z) to suspend JDBC client, when the query in #1 is running; 3. psql -c 'DROP TABLE tbl', then the request will be hang; 4. psql -c 'select * from tb', the request will be hang as well; 5.When backend finishes query in #1, it replies message to client and HANG at send() method, we may see following stack info: #0 0x7fc8f4972b41 in send () from /lib64/libpthread.so.0 #1 0x0078ff9a in secure_write () #2 0x0079c225 in internal_flush () #3 0x0079c0e2 in internal_putbytes () #4 0x0079c457 in pq_putmessage () #5 0x0079cff3 in pq_endmessage () #6 0x004c831c in printtup () #7 0x007228b1 in ExecSelect () #8 0x007226ec in ExecutePlan () #9 0x0071e461 in ExecutorRun () #10 0x008fa31a in PortalRunSelect () #11 0x008f9efa in PortalRun () #12 0x008f1458 in exec_execute_message () #13 0x008f4f91 in PostgresMain () #14 0x0089d8a6 in BackendRun () #15 0x0089cd18 in BackendStartup () #16 0x00896e89 in ServerLoop () #17 0x00895ea8 in PostmasterMain () #18 0x007b1baa in main () Wish this helps. > 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 > > 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)
[jira] [Commented] (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=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,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 > 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)
[jira] [Commented] (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=16237193#comment-16237193 ] Lin Wen commented on HAWQ-1530: --- Hi, Do you have a detailed reproduce for this bug? I want to reproduce it in my environment, is it a must to install Aqua Data Studio? Can we open multiple psql sessions and run some queries, etc, to reproduce it? Thanks! > 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)
[jira] [Commented] (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=16235451#comment-16235451 ] Kuien Liu commented on HAWQ-1530: - any progress on this issue? plz prioritize it if possible, thanks. > 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)
