[HAWQ-800] Less tuple is inserted due to data locality information is not refreshed and dispatched in prepared statement

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Resolved
• Affects Version/s: 2.0.0.0-incubating
• Fix Version/s: 2.0.0.0-incubating
• Component/s: Core, Dispatcher, Query Execution, Resource Manager
• Assignee: Ruilong Huo
• Reporter: Ruilong Huo
• Created: 12/Jun/16 11:02
• Updated: 14/Jul/16 17:59
• Resolved: 14/Jul/16 17:57

Description

In either explicit (SQL) or implicit (UDF) prepared statement, there is less tuple inserted if we run a prepared "insert into t select * from t" plan multiple times in a transaction.

Below is a simple case to reproduce this issue. For a more complicated example, you may refer to attached proba_execute and proba.
1. There should be 8 tuples, however there is only 4 in hawq 2.0

drop table if exists t;
DROP TABLE

create table t (id int);
CREATE TABLE

insert into t values (1);
INSERT 0 1

CREATE OR REPLACE FUNCTION f_load()
RETURNS TEXT
LANGUAGE plpgsql
AS
$body$
DECLARE
    l_rec RECORD;
    l_itm RECORD;
BEGIN

    FOR l_rec IN ( SELECT generate_series(1, 3) AS id )
    LOOP
        INSERT INTO t SELECT * FROM t;
    END LOOP;

    RETURN 'done';

END;
$body$
;
CREATE FUNCTION

SELECT f_load();
 f_load
--------
 done
(1 row)

SELECT * FROM t;
 id
----
  1
  1
  1
  1
(4 rows)

2. There are 8 tuples as expected in hawq 1.x

drop table if exists t;
DROP TABLE

create table t (id int);
psql:temp.sql:3: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE

insert into t values (1);
INSERT 0 1

CREATE OR REPLACE FUNCTION f_load()
RETURNS TEXT
LANGUAGE plpgsql
AS
$body$
DECLARE
    l_rec RECORD;
    l_itm RECORD;

BEGIN

    FOR l_rec IN ( SELECT generate_series(1, 3) AS id )
    LOOP
        INSERT INTO t SELECT * FROM t;
    END LOOP;

    RETURN 'done';

END;
$body$
;
CREATE FUNCTION

SELECT f_load();
 f_load
--------
 done
(1 row)

SELECT * FROM t;
 id
----
  1
  1
  1
  1
  1
  1
  1
  1
(8 rows)

Issue Links

• incorporates: HAWQ-850 Planner supports refineCachedPlan interface.
• relates to: HAWQ-835 Cannot retrieve tuple from temp table created in function
• links to: GitHub Pull Request #749