[jira] [Closed] (HAWQ-738) Allocate query resource twice in function call through jdbc

[Ruilong Huo (JIRA)]

 [ 
https://issues.apache.org/jira/browse/HAWQ-738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ruilong Huo closed HAWQ-738.


> Allocate query resource twice in function call through jdbc
> ---
>
> Key: HAWQ-738
> URL: https://issues.apache.org/jira/browse/HAWQ-738
> Project: Apache HAWQ
>  Issue Type: Bug
>  Components: Core
>Affects Versions: 2.0.0
>Reporter: Ruilong Huo
>Assignee: Ruilong Huo
> Fix For: 2.0.0
>
>
> It allocates query resource twice in function call through jdbc, one in 
> parse, and the other in bind. Though the same thing works with psql.
> Use runme.sh in attached bug.zip to reproduce the issue. It may raise below 
> error on host with limited resource (i.e., low memory, etc).
> {noformat}
> [gpadmin@localhost debug]$ ./runme.sh 
> java -classpath /home/gpadmin/debug/Bug.jar:/home/gpadmin/debug/gpdb.jar Bug 
> localhost 5432 gpadmin gpadmin changeme
> gpServer: hdp23
> gpPort: 5432
> gpDatabase: gpadmin
> gpUserName: gpadmin
> gpPassword: changeme
> DriverManager.getConnection("jdbc:postgresql://hdp23:5432/gpadmin")
> trying sun.jdbc.odbc.JdbcOdbcDriver
> *Driver.connect (jdbc:postgresql://hdp23:5432/gpadmin)
> trying org.postgresql.Driver
> getConnection returning org.postgresql.Driver
> strSQL: DROP TABLE IF EXISTS public.debug;
> CREATE TABLE public.debug
> (id int, foo_bar text)
> DISTRIBUTED RANDOMLY;
> strSQL: INSERT INTO public.debug
> SELECT i, 'foo_' || i from generate_series(1,100) AS i;
> strSQL: CREATE OR REPLACE FUNCTION public.fn_debug() RETURNS text AS
> $$
> DECLARE
>   v_return text;
> BEGIN
>   SELECT foo_bar
>   INTO v_return
>   FROM public.debug
>   WHERE id = 1;
>   RETURN v_return;
> END;
> $$
> LANGUAGE plpgsql;
> strSQL: SELECT public.fn_debug()
> org.postgresql.util.PSQLException: ERROR: failed to acquire resource from 
> resource manager, session 32 deadlock is detected (pquery.c:804)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>   at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
>   at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
>   at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
>   at Bug.getFunctionResults(Bug.java:144)
>   at Bug.main(Bug.java:41)
> SQLException: SQLState(XX000)
> ERROR: failed to acquire resource from resource manager, session 32 deadlock 
> is detected (pquery.c:804)
> Exception in thread "main" java.sql.SQLException: ERROR: failed to acquire 
> resource from resource manager, session 32 deadlock is detected (pquery.c:804)
>   at Bug.main(Bug.java:49)
> {noformat}
> while the expected result is as below:
> {noformat}
> [gpadmin@localhost hawq_bug]$ ./runme.sh
> java -classpath 
> /home/gpadmin/huor/hawq_bug/Bug.jar:/home/gpadmin/huor/hawq_bug/gpdb.jar Bug 
> localhost 5432 gptest gpadmin changeme
> gpServer: localhost
> gpPort: 5432
> gpDatabase: gptest
> gpUserName: gpadmin
> gpPassword: changeme
> DriverManager.getConnection("jdbc:postgresql://localhost:5432/gptest")
> trying sun.jdbc.odbc.JdbcOdbcDriver
> *Driver.connect (jdbc:postgresql://localhost:5432/gptest)
> trying org.postgresql.Driver
> getConnection returning org.postgresql.Driver
> strSQL: DROP TABLE IF EXISTS public.debug;
> CREATE TABLE public.debug
> (id int, foo_bar text)
> DISTRIBUTED RANDOMLY;
> SQLWarning:
> strSQL: INSERT INTO public.debug
> SELECT i, 'foo_' || i from generate_series(1,100) AS i;
> strSQL: CREATE OR REPLACE FUNCTION public.fn_debug() RETURNS text AS
> $$
> DECLARE
> v_return text;
> BEGIN
> SELECT foo_bar
> INTO v_return
> FROM public.debug
> WHERE id = 1;
> RETURN v_return;
> END;
> $$
> LANGUAGE plpgsql;
> strSQL: SELECT public.fn_debug()
> output: foo_1
> {noformat}
> If you look into the pg_log on master, you can see it allocate query resource 
> twice for the function call:
> {noformat}
> rhuo-mbp:jdbc rhuo$ cat hawq-2016-05-14_00.csv
> 2016-05-16 13:50:50.255504 
> EDT,,,p4522,th4240651520,con4,,seg-1,"LOG","0","Resource 
> manager adjusts segment hdp23.localdomain original resource capacity from 
> (2048 MB, 48 CORE) to (2048 MB, 2 CORE)",,,0,,"resourcepool.c",4688,
> 2016-05-16 13:51:20.275204 
> EDT,,,p4522,th4240651520,con4,,seg-1,"LOG","0","Resource 
> manager adjusts segment hdp23.localdomain original resource capacity from 
> (2048 MB, 48 CORE) to (2048 MB, 2 CORE)",,,0,,"resourcepool.c",4688,
> 2016-05-16 

[jira] [Closed] (HAWQ-738) Allocate query resource twice in function call through jdbc

[Ruilong Huo (JIRA)]

 [ 
https://issues.apache.org/jira/browse/HAWQ-738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ruilong Huo closed HAWQ-738.


> Allocate query resource twice in function call through jdbc
> ---
>
> Key: HAWQ-738
> URL: https://issues.apache.org/jira/browse/HAWQ-738
> Project: Apache HAWQ
>  Issue Type: Bug
>  Components: Core
>Affects Versions: 2.0.0
>Reporter: Ruilong Huo
>Assignee: Ruilong Huo
> Fix For: 2.1.0
>
>
> It allocates query resource twice in function call through jdbc, one in 
> parse, and the other in bind. Though the same thing works with psql.
> Use runme.sh in attached bug.zip to reproduce the issue. It may raise below 
> error on host with limited resource (i.e., low memory, etc).
> {noformat}
> [gpadmin@localhost debug]$ ./runme.sh 
> java -classpath /home/gpadmin/debug/Bug.jar:/home/gpadmin/debug/gpdb.jar Bug 
> localhost 5432 gpadmin gpadmin changeme
> gpServer: hdp23
> gpPort: 5432
> gpDatabase: gpadmin
> gpUserName: gpadmin
> gpPassword: changeme
> DriverManager.getConnection("jdbc:postgresql://hdp23:5432/gpadmin")
> trying sun.jdbc.odbc.JdbcOdbcDriver
> *Driver.connect (jdbc:postgresql://hdp23:5432/gpadmin)
> trying org.postgresql.Driver
> getConnection returning org.postgresql.Driver
> strSQL: DROP TABLE IF EXISTS public.debug;
> CREATE TABLE public.debug
> (id int, foo_bar text)
> DISTRIBUTED RANDOMLY;
> strSQL: INSERT INTO public.debug
> SELECT i, 'foo_' || i from generate_series(1,100) AS i;
> strSQL: CREATE OR REPLACE FUNCTION public.fn_debug() RETURNS text AS
> $$
> DECLARE
>   v_return text;
> BEGIN
>   SELECT foo_bar
>   INTO v_return
>   FROM public.debug
>   WHERE id = 1;
>   RETURN v_return;
> END;
> $$
> LANGUAGE plpgsql;
> strSQL: SELECT public.fn_debug()
> org.postgresql.util.PSQLException: ERROR: failed to acquire resource from 
> resource manager, session 32 deadlock is detected (pquery.c:804)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>   at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
>   at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
>   at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
>   at Bug.getFunctionResults(Bug.java:144)
>   at Bug.main(Bug.java:41)
> SQLException: SQLState(XX000)
> ERROR: failed to acquire resource from resource manager, session 32 deadlock 
> is detected (pquery.c:804)
> Exception in thread "main" java.sql.SQLException: ERROR: failed to acquire 
> resource from resource manager, session 32 deadlock is detected (pquery.c:804)
>   at Bug.main(Bug.java:49)
> {noformat}
> while the expected result is as below:
> {noformat}
> [gpadmin@localhost hawq_bug]$ ./runme.sh
> java -classpath 
> /home/gpadmin/huor/hawq_bug/Bug.jar:/home/gpadmin/huor/hawq_bug/gpdb.jar Bug 
> localhost 5432 gptest gpadmin changeme
> gpServer: localhost
> gpPort: 5432
> gpDatabase: gptest
> gpUserName: gpadmin
> gpPassword: changeme
> DriverManager.getConnection("jdbc:postgresql://localhost:5432/gptest")
> trying sun.jdbc.odbc.JdbcOdbcDriver
> *Driver.connect (jdbc:postgresql://localhost:5432/gptest)
> trying org.postgresql.Driver
> getConnection returning org.postgresql.Driver
> strSQL: DROP TABLE IF EXISTS public.debug;
> CREATE TABLE public.debug
> (id int, foo_bar text)
> DISTRIBUTED RANDOMLY;
> SQLWarning:
> strSQL: INSERT INTO public.debug
> SELECT i, 'foo_' || i from generate_series(1,100) AS i;
> strSQL: CREATE OR REPLACE FUNCTION public.fn_debug() RETURNS text AS
> $$
> DECLARE
> v_return text;
> BEGIN
> SELECT foo_bar
> INTO v_return
> FROM public.debug
> WHERE id = 1;
> RETURN v_return;
> END;
> $$
> LANGUAGE plpgsql;
> strSQL: SELECT public.fn_debug()
> output: foo_1
> {noformat}
> If you look into the pg_log on master, you can see it allocate query resource 
> twice for the function call:
> {noformat}
> rhuo-mbp:jdbc rhuo$ cat hawq-2016-05-14_00.csv
> 2016-05-16 13:50:50.255504 
> EDT,,,p4522,th4240651520,con4,,seg-1,"LOG","0","Resource 
> manager adjusts segment hdp23.localdomain original resource capacity from 
> (2048 MB, 48 CORE) to (2048 MB, 2 CORE)",,,0,,"resourcepool.c",4688,
> 2016-05-16 13:51:20.275204 
> EDT,,,p4522,th4240651520,con4,,seg-1,"LOG","0","Resource 
> manager adjusts segment hdp23.localdomain original resource capacity from 
> (2048 MB, 48 CORE) to (2048 MB, 2 CORE)",,,0,,"resourcepool.c",4688,
> 2016-05-16