You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hawq.apache.org by "Grant Krieger (JIRA)" <ji...@apache.org> on 2017/11/03 07:47:00 UTC
[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,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
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,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)