[jira] [Commented] (HAWQ-412) Allocate query resource for multiple EXECUTIONs in explicit and implicit prepared statement

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HAWQ-412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15148242#comment-15148242 ] 

ASF GitHub Bot commented on HAWQ-412:
-------------------------------------

GitHub user huor opened a pull request:

    https://github.com/apache/incubator-hawq/pull/345

    HAWQ-412. Allocate query resource for multiple EXECUTIONs in explicit and implicit prepared statement

    

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/huor/incubator-hawq huor_madlib

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/incubator-hawq/pull/345.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #345
    
----

----


> Allocate query resource for multiple EXECUTIONs in explicit and implicit prepared statement
> -------------------------------------------------------------------------------------------
>
>                 Key: HAWQ-412
>                 URL: https://issues.apache.org/jira/browse/HAWQ-412
>             Project: Apache HAWQ
>          Issue Type: Bug
>          Components: Core, Query Execution
>    Affects Versions: 2.0.0-beta-incubating
>            Reporter: Ruilong Huo
>            Assignee: Ruilong Huo
>            Priority: Critical
>             Fix For: 2.0.0
>
>
> There are several scenarios that prepared statement can be used:
> 1) Explicit prepared statement, either in SQL statement or in JDBC/ODBC
> 2) Implicit prepared statement, i.e. (table) functions, UDFs
> In prepared statement, the query is planned once, while the plan is executed multiple times. We need to allocate resource for the multiple executions of the plan. Otherwise, it may error out or even core dump.
> Here are several cases that hit this problem:
> 1) Explicit prepared statement in (table) function and UDF
> {noformat}
> CREATE OR REPLACE FUNCTION f()
> RETURNS VOID AS $$
>     plpy.execute("DROP TABLE IF EXISTS t")
>     plpy.execute("CREATE TABLE t(key TEXT, value DOUBLE PRECISION)")
>     sql_plan = plpy.prepare("""
>         INSERT INTO test
>         SELECT 
>             unnest($1) as key, 
>             unnest($2) as value
>         """, ['text[]', 'double precision[]'])
>     plpy.execute(sql_plan,  [['a'], [1]])
>     plpy.execute(sql_plan,  [['a'], [1]])
> $$ LANGUAGE plpythonu VOLATILE;
> SELECT f();
> Error:
> ERROR: plpy.SPIError: could not serialize unrecognized node type: 45758608 (plpython.c:4651) CONTEXT: Traceback (most recent call last): PL/Python function "f", line 13, in <module> [['a'], [1]]) PL/Python function "f"
> {noformat}
> 2) Implicit prepared statement in (table) function and UDF
> {noformat}
> CREATE TABLE users (
> 	fname text not null,
> 	lname text not null,
> 	username text,
> 	userid serial
> 	-- , PRIMARY KEY(lname, fname) 
> 	) DISTRIBUTED BY (userid);
> INSERT INTO users (fname, lname, username) VALUES ('jane', 'doe', 'j_doe');
> INSERT INTO users (fname, lname, username) VALUES ('john', 'doe', 'johnd');
> INSERT INTO users (fname, lname, username) VALUES ('willem', 'doe', 'w_doe');
> INSERT INTO users (fname, lname, username) VALUES ('rick', 'smith', 'slash');
> CREATE FUNCTION spi_prepared_plan_test_one(a text)
> RETURNS text
> AS
> '
> if not SD.has_key("myplan"):
>     q = "SELECT count(*) FROM users WHERE lname = $1"
>     SD["myplan"] = plpy.prepare(q, [ "text" ])
> try:
>     rv = plpy.execute(SD["myplan"], [a])
>     return "there are " + str(rv[0]["count"]) + " " + str(a) + "s"
> except Exception, ex:
>     plpy.error(str(ex))
> return None
> '
> LANGUAGE plpythonu;
> select spi_prepared_plan_test_one('doe');
> select spi_prepared_plan_test_one('doe');
> select spi_prepared_plan_test_one('smith');
> {noformat}
> 3) Combined explicit and implicit prepared statement
> {noformat}
> PREPARE fooplan (int, text) AS
>     SELECT spi_prepared_plan_test_one($2);
> EXECUTE fooplan(1, 'doe');
> EXECUTE fooplan(2, 'doe');
> EXECUTE fooplan(3, 'doe');
> EXECUTE fooplan(4, 'smith');
> EXECUTE fooplan(5, 'smith');
> EXECUTE fooplan(6, 'smith');
> EXECUTE fooplan(7, 'smith3');
> EXECUTE fooplan(8, 'smith3');
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)