1

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.

1 Answer 1

0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.