[pgpool-general: 3386] Re: Problem with parallel query mode
Andre Eduardo Bento Garcia
andreedugarcia at gmail.com
Sat Dec 27 03:16:09 JST 2014
Complemented ...
When I related hl.p_h_lineitem = lli.p_h_lineitem this message is displayed.
Attached my pgpool.conf and insert to the rules script.
Thanks again.
2014-12-26 15:39 GMT-02:00 Andre Eduardo Bento Garcia <
andreedugarcia at gmail.com>:
> Friends,
> I am using pgpool-II 3.4, postgres 9.3 and ubuntu 14.4.
> I implemented a query parallel in two nodes with the same configuration
> but diferent hardware.
> I run query into distribuited tables (3 milion lines each node)
> The tables (l_lineitem, h_lineitem) are distribuited.
> The tables (h_orders, h_supplier) are replicated.
>
> My pgpool.conf is configured parallel_mode = on and replicate_mode = on
> and load_balance = false
>
> Of course:
> I inserted on pgpool_catalog.dist_def and pgpool_catalog.replicate_def
> I created a function for each rule table like
> pgpool_catalog.dist_def_h_lineitem and pgpool_catalog.dist_def_l_lineitem
> I specified a rule: if the value of pk for each table is less than 3000000
> goes to the node 1 else goes to the node 0.
>
> So when I run a simple query from port 9999, I get results ok.
>
> bench_parallel=# select count(0) from l_lineitem lli, h_orders ho,
> h_supplier hs where lli.p_h_orderkey = ho.p_h_orderkey and
> lli.p_h_suppkey = hs.p_h_suppkey ;
> count
> ---------
> 6001215
> (1 registro)
>
> bench_parallel=# select count(0) from l_lineitem lli, h_lineitem hli where
> lli.p_h_lineitem = hli.p_h_lineitem ;
> count
> ---------
> 6001215
> (1 registro)
>
>
> So wnen I run a little complex query I received this message :
>
>
> bench_parallel=# select count(0) from h_lineitem hl,l_lineitem lli,
> h_orders ho, h_supplier hs where lli.p_h_orderkey = ho.p_h_orderkey and
> lli.p_h_suppkey = hs.p_h_suppkey and hl.p_h_lineitem =lli.p_h_lineitem;
>
> ERROR: pgpool2 sql restriction(notice from dblink)
>
> DETALHE: SELECT count(0) AS count FROM dblink('host=andre-ubuntu-01
> dbname=bench_parallel port=9999 user=postgres','SELECT
> pool_parallel("SELECT hl.p_h_lineitem FROM h_lineitem AS hl WHERE ( (TRUE
> AND TRUE) AND TRUE)")',false) AS hl("pool_c$0" integer),dblink('
>
> Its seems that the table (l_lineitem) doesn't accept to relate on the more
> than four. I was thinking that it was my problem, but the relationship
> works individually.
>
> I read at this mailinglist and I found this topic about restriction
> complex query (
> http://www.sraoss.jp/pipermail/pgpool-general/2013-November/002312.html)
>
>
> So I need your help.
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141226/6f778270/attachment-0001.html>
-------------- next part --------------
insert into pgpool_catalog.dist_def (dbname,schema_name, table_name,col_name,col_list,type_list,dist_def_func)
values ('bench_parallel',
'public',
'h_lineitem',
'p_h_lineitem',
ARRAY['p_h_lineitem','l_orderkey','l_linenumber','origem','loaddate'],
ARRAY['integer','integer','integer','character(50)','timestamp without time zone'],'pgpool_catalog.dist_def_h_lineitem' );
insert into pgpool_catalog.dist_def (dbname,schema_name, table_name,col_name,col_list,type_list,dist_def_func)
values ('bench_parallel',
'public',
'l_lineitem',
'p_h_lineitem',
ARRAY['p_l_lineitem','p_h_lineitem','p_h_orderkey','p_h_partkey','p_h_suppkey','origem','loaddate'],
ARRAY['integer' ,'integer' ,'integer' ,'integer' ,'integer','character(50)','timestamp without time zone'],'pgpool_catalog.dist_def_l_lineitem' );
insert into pgpool_catalog.dist_def (dbname,schema_name, table_name,col_name,col_list,type_list,dist_def_func)
values ('bench_parallel',
'public',
's_lineitem',
'p_h_lineitem',
ARRAY['p_s_lineitem','p_h_lineitem','l_quantity','l_extendedprice','l_discount','l_tax' ,'l_returnflag','l_linestatus','l_shipdate','l_commitdate','l_receipdate','l_shipinstruct','l_shipmode' ,'l_comment' ,'origem' ,'loaddate' ,'loadenddate'],
ARRAY['integer' ,'integer' ,'numeric' ,'numeric' ,'numeric' ,'numeric','character(1)','character(1)','date' ,'date' ,'date' ,'character(25)' ,'character(10)','character(44)','character(50)','timestamp without time zone','timestamp without time zone DEFAULT'],
'pgpool_catalog.dist_def_s_lineitem' );
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'h_region',
ARRAY['p_h_regionkey', 'r_regionkey', 'origem','loaddate'],
ARRAY['integer', 'integer', 'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
's_region',
ARRAY['p_s_regionkey', 'p_h_regionkey','r_name','r_comment', 'origem','loaddate','loadenddate'],
ARRAY['integer', 'integer', 'character(25)','character(152)','character(50)','timestamp without time zone DEFAULT','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'h_nation',
ARRAY['p_h_nationkey', 'n_nationkey', 'origem','loaddate'],
ARRAY['integer', 'integer', 'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
's_nation',
ARRAY['p_s_nationkey', 'p_h_nationkey','n_name','n_comment', 'origem','loaddate','loadenddate'],
ARRAY['integer', 'integer', 'character(25)','character(152)','character(50)','timestamp without time zone DEFAULT','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'l_nation_region',
ARRAY['p_l_natregkey', 'p_h_nationkey', 'p_h_regionkey','origem','loaddate'],
ARRAY['integer', 'integer', 'integer' ,'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'h_supplier',
ARRAY['p_h_suppkey', 's_suppkey', 'origem','loaddate'],
ARRAY['integer', 'integer', 'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
's_supplier',
ARRAY['p_s_suppkey', 'p_h_suppkey','s_name','s_address','s_phone','s_acctbal','s_comment', 'origem','loaddate','loadenddate'],
ARRAY['integer', 'integer','character(25)','character(40)','character(15)','numeric','character(101)','character(50)','timestamp without time zone DEFAULT','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'l_supplier_nation',
ARRAY['p_l_supnatkey', 'p_h_suppkey', 'p_h_nationkey','origem','loaddate'],
ARRAY['integer', 'integer', 'integer' ,'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'h_customer',
ARRAY['p_h_suppkey', 'c_custkey', 'origem','loaddate'],
ARRAY['integer', 'integer', 'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
's_customer',
ARRAY['p_s_custkey', 'p_h_custkey','c_name','c_address','c_phone','c_acctbal','c_mktsegment','c_comment', 'origem','loaddate','loadenddate'],
ARRAY['integer', 'integer','character(25)','character(40)','character(15)','numeric','character(10)','character(117)','character(50)','timestamp without time zone DEFAULT','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'l_customer_nation',
ARRAY['p_l_cusnatkey', 'p_h_custkey', 'p_h_nationkey','origem','loaddate'],
ARRAY['integer', 'integer', 'integer' ,'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'h_part',
ARRAY['p_h_partkey', 'p_partkey', 'origem','loaddate'],
ARRAY['integer', 'integer', 'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
's_part',
ARRAY['p_s_partkey', 'p_h_partkey','p_name','p_mfgr','p_brand','p_type','p_size','p_container','p_retailprice','p_comment', 'origem','loaddate','loadenddate'],
ARRAY['integer', 'integer','character(55)','character (25)','character(10)','character(25)','integer','character(10)','numeric','character(23)','character(50)','timestamp without time zone DEFAULT','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'l_partsupp',
ARRAY['p_l_parsupkey', 'p_h_partkey', 'p_h_suppkey','origem','loaddate'],
ARRAY['integer', 'integer', 'integer' ,'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
's_partsupp',
ARRAY['p_s_parsupkey', 'p_l_parsupkey','ps_availqty','ps_supplycost','ps_comment', 'origem','loaddate','loadenddate'],
ARRAY['integer', 'integer','integer','numeric','character(199)','character varying(50)','timestamp without time zone DEFAULT','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'h_orders',
ARRAY['p_h_orderkey', 'o_orderkey', 'origem','loaddate'],
ARRAY['integer', 'integer', 'character(50)','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
's_orders',
ARRAY['p_s_orderkey', 'p_h_orderkey','o_orderstatus','o_totalprice','o_orderdate','o_orderpriority','o_clerk',
'o_shippriority','o_comment', 'origem','loaddate','loadenddate'],
ARRAY['integer', 'integer','character(1)','numeric','date','character(15)','character(15)','integer','character(79)','character(50)','timestamp without time zone DEFAULT','timestamp without time zone DEFAULT']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'l_orders_customer',
ARRAY['p_l_ordcuskey', 'p_h_orderkey', 'p_h_custkey','origem','loaddate'],
ARRAY['integer', 'integer', 'integer' ,'character(50)','timestamp without time zone DEFAULT']
);
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool.conf
Type: application/octet-stream
Size: 31002 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141226/6f778270/attachment-0001.obj>
More information about the pgpool-general
mailing list