Failed To Insert Data Into Tables Using Postgres-XL and 4 Datanode

Question

Recently, I have installed Postgres-XL-9.5r1.5 in a R630, 16 cores, 16 GB RAM. I configured 1 GTM, 1 Coordinator, and 4 Datanodes. I created a simple table sales_small_cat

CREATE TABLE sales_small_cat
(
   date_sales            timestamp    NOT NULL,
   store_sid             integer      NOT NULL,
   branch_sid            integer,
   small_cat_sid         integer      NOT NULL,
   total_receipt_amount  integer,
   qty_sales_nonpromo    numeric,
   qty_sales_promo_rep   numeric,
   qty_sales_promo_avp   numeric,
   total_sales           numeric,
   pi                    numeric,
   is_festive            boolean,
   is_store_closed       boolean
) 
DISTRIBUTED BY (store_sid)
TO NODE (dnode1,dnode2,dnode3,dnode4);
ALTER TABLE sales_small_cat
   ADD CONSTRAINT pk_sales_small_cat
   PRIMARY KEY (store_sid, small_cat_sid, date_sales);

I want to insert dummy data using

WITH
trxDates AS ( SELECT generate_series('2017-01-01'::DATE,'2017-01-01'::DATE,'1 day'::INTERVAL) AS trx_date),
idmStores AS ( SELECT generate_series(1,100,1) AS store_sid),
scat AS ( SELECT generate_series(1,40,1) AS scat_sid),
temp AS (
  SELECT trx_date,store_sid,round(random()*10+1) AS branch_sid,scat_sid
  FROM trxDates CROSS JOIN idmStores CROSS JOIN scat
),
temp2 AS (
  SELECT 
    *, round(random()*1000) AS receipt_amount,
  random()*1000 AS non_promo, random()*1000 AS promo_rep, random()*1000 AS promo_avp
  FROM temp
),
finale AS (
  SELECT *,(non_promo+promo_rep+promo_avp) AS total_sales, random()*1000 AS purchase_index, false AS is_festive, false AS is_store_closed
  FROM temp2
  ORDER BY 1,2,4
)
INSERT INTO sales_small_cat
SELECT * FROM finale;

And I get

An error occurred when executing the SQL command:
WITH
trxDates AS ( SELECT generate_series('2017-01-01'::DATE,'2017-01-01'::DATE,'1 day'::INTERVAL) AS trx_date),
idmStores AS ( SELECT generate_series...

ERROR: unrecognized node type: 119

Execution time: 0.02s
1 statement failed.

I traced the log of datanode below:

[postgres@serveragci ~]$ tail -f pgxc/nodes/dn_master/dnode1/pg_log/datanode-20170508.log
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan
LOG:  disconnection: session time: 0:15:19.504 user=postgres database=postgres host=192.168.101.111 port=37161
LOG:  disconnection: session time: 0:16:52.181 user=admindb database=test host=192.168.101.111 port=37209
LOG:  connection received: host=192.168.101.111 port=37432
LOG:  connection authorized: user=admindb database=test
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan

Is it a bug or my mistake? Please, enlight me. Thank you very much.

Answer 1

Finally, I found it as my mistake because I am still affected by ordinary PostgreSQL (9.4) behaviour. After I added ::INTEGER and LIMIT 1000000 in the DML below, the data insertion is a success.

...
temp2 AS (
  SELECT 
    *, round(random()*1000)::INTEGER AS receipt_amount,
  random()*1000 AS non_promo, random()*1000 AS promo_rep, random()*1000 AS promo_avp
  FROM temp
),
...

  ORDER BY 1,2,4
)
INSERT INTO sales_small_cat
SELECT * FROM finale LIMIT 1000000;

Frankly, I am still confused. Why does Postgres-XL need the LIMIT clause?