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

1 vote
1 answer
399 views
                          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.
                        
Asked by Dwi Santoso (11 rep)
May 8, 2017, 09:48 AM
Last activity: May 9, 2017, 01:24 AM
Failed To Insert Data Into Tables Using Postgres-XL and 4 Datanode

Related Questions
