[pgpool-general: 3390] Why does it happens ? (Problem with parallel query mode (pgpool2 sql restriction(notice from dblink)))

Andre Eduardo Bento Garcia andreedugarcia at gmail.com
Tue Dec 30 22:29:25 JST 2014


Friends,
I am using pgpool-II  3.4, postgres 9.3 and ubuntu 14.04
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 lines into 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 variable for each table is less than 3000000
    goes to the node 1
else
    goes to the node 0
end if

So when I run simples querys 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
				  /**********This line I got this errors**************/
				  and   hl.p_h_lineitem  = lli.p_h_lineitem;
                  /**********This line I got this errors**************/
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('



So I can't to end up my querys. And I don't no where the problema is ?
I need to run querys about benchmarck tpc-h to compare times both
situations with distribuited tables or without distribuited tables.

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)

Is this my problem related with ?

I atached my pgpool.conf and inserts into control tables.

So I need your help.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141230/ec1eb353/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: reproblemwithparallelquerymodepgpool2sqlrestrictio.zip
Type: application/zip
Size: 7102 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141230/ec1eb353/attachment.zip>


More information about the pgpool-general mailing list