[jira] [Created] (HAWQ-412) Allocate query resource for multiple EXECUTIONs in prepared statement

["Ruilong Huo (JIRA)" <ji...@apache.org>]

Ruilong Huo created HAWQ-412:
--------------------------------

             Summary: Allocate query resource for multiple EXECUTIONs in prepared statement
                 Key: HAWQ-412
                 URL: https://issues.apache.org/jira/browse/HAWQ-412
             Project: Apache HAWQ
          Issue Type: Bug
          Components: Core, Query Execution
            Reporter: Ruilong Huo
            Assignee: George Caragea


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)