1

Today,I defined a function,using the insert statements in a loop. But HAWQ returned an error:

ERROR:  could not serialize unrecognized node type: 43983632 (outfast.c:4742)
CONTEXT:  SQL statement "insert into t(id,value) values(1,0.1)"
PL/pgSQL function "test_function" line 6 at SQL statement

I did some testing and found that when I use the 'insert statements' in the loop,it will be reported as a mistake. If I delete the relevant 'insert statements',It can run properly.

Here is an example of a test:

 CREATE OR REPLACE FUNCTION test_function()
  RETURNS int AS
$BODY$
declare 
 number int;
begin  
 number := 1;
 while number <= 10 loop
  insert into t(id,value) values(1,0.1);
  number := number+1;
 end loop;
 return number;
end
$BODY$
  LANGUAGE plpgsql ;

Then I use 'select test_function();' to call the function.It will returned an error mentioned above.

Does this mean that I can not use the SQL statements in a loop with plpgsql ?

Thanks. Best regards.

Songjs
  • 13
  • 2

2 Answers2

1

You'll want to avoid singleton statements with HAWQ but I am a little surprised it doesn't work. You'll want to use set based operations instead. 

    CREATE TABLE t (id int, value numeric);

    CREATE OR REPLACE FUNCTION test_function() RETURNS int AS
    $BODY$
    declare 
            number int;
    begin       
            insert into t (id, value) select 1, 0.1 from generate_series(1, 10);
            return 10;
    end
    $BODY$
    LANGUAGE plpgsql;

For such a simple example, you could use a sql function instead which has less overhead than plpgsql. 

    DROP FUNCTION test_function();

    CREATE OR REPLACE FUNCTION test_function() RETURNS void AS
    $BODY$
            insert into t (id, value) select 1, 0.1 from generate_series(1, 10);
    $BODY$
    LANGUAGE sql;

Both of these functions do all of the work in a single statement rather than executing 10 separate ones. I tested both in HAWQ and both work.

And here is a workaround if you must do singleton insert statements in a loop with HAWQ.

    CREATE OR REPLACE FUNCTION test_function()
      RETURNS int AS
    $BODY$
    declare 
            number int;
            v_sql text;
    begin       
            number := 1;
            while number <= 10 loop
                    v_sql := 'insert into t(id,value) values(1,0.1)';
                    execute v_sql;
                    number := number+1;
            end loop;
            return number;
    end
    $BODY$
      LANGUAGE plpgsql ;
Jon Roberts
  • 2,068
  • 1
  • 9
  • 11
1

This is actually an issue that has been addressed in hawq 2.0. You may refer to latest https://github.com/apache/incubator-hawq for reference.

Here are the result:

CREATE TABLE t (id INT, value DOUBLE PRECISION);
CREATE TABLE

CREATE OR REPLACE FUNCTION test_function()
RETURNS int AS
$BODY$
declare
    number int;
begin
    number := 1;
    while number <= 10 loop
        insert into t(id, value) values(1, 0.1);
        number := number+1;
    end loop;

    return number;
end
$BODY$
LANGUAGE plpgsql;
CREATE FUNCTION



SELECT test_function();
 test_function
---------------
            11
(1 row)

SELECT * FROM t;
 id | value
----+-------
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
(10 rows)



SELECT * FROM test_function();
 test_function
---------------
            11
(1 row)

SELECT * FROM t;
 id | value
----+-------
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
  1 |   0.1
(10 rows)
huor
  • 116
  • 4