[Pgpool-general] pgpool hangs

[Yoshiharu Mori]

Hi

Are not you setting pgpool.conf as follows?

num_init_children = 1 or small value

It is necessary to think about the number connected with pgpool from dblink. 

Please set num_init_children as a standard by the following expressions. 

num_init_children = be assumed Connected number * 
                   (1 + maximum number of tables used in Query)


> *Hi,
> 
> I've installed dblink and pgpool according to the instructions and I
> encountered some off behavior I was hoping you could help me with.
> It seems that simple queries work just fine (such as "select * from **
> pool.users**" work just fine and I get the merged data from both databases)
> however more complex ones cause pgpool to * *hang **(for example "select
> count(*) from **pool.users** ").
> I tried using dblink directly to access both databases from each other, and
> it worked just fine.**
> 
> **The last log entry on pgpool is (where it hangs): **
> **DEBUG: pid 4469: OneNode_do_command: Query:  SELECT  sum(** args_$0**) AS
> COUNT FROM  dblink('host=localhost dbname=pool port=5433 user=pool','SELECT
> pool_parallel("select count(*)  FROM pool.users")') AS users(
> **args_$0 **bigint
> )*
> **
> *
> 
> * *The stack trace is here:**
> **#0  0x0060c7a2 in _dl_sysinfo_int80 () from /lib/ld- linux.so.2*
> *#1  0x006d945d in ___newselect_nocancel () from /lib/tls/libc.so.6*
> *#2  0x0805767b in pool_check_fd (cp=0xfffffdfe, notimeout=0) at
> pool_process_query.c:2457 *
> *#3  0x0805be2a in pool_read (cp=0x9199308, buf=0xbfee4537, len=1) at
> pool_stream.c:126*
> *#4  0x0805acf2 in OneNode_do_command (frontend=0x919c428,
> backend=0x9199308, *
> *    query=0x91a6034 " SELECT  sum(args_$0) AS COUNT   FROM
> dblink('host=localhost dbname=pool port=5433 user=pool','SELECT
> pool_parallel(\"select count(*)  FROM pool.users\")') AS users( args_$0
> bigint )", database=0xb7f34000 "pool")*
> *    at pool_process_query.c:3941*
> * *
> *
> **We have two database machines, running postgresql 8.2.4 (one is a 32 bit
> Linux machine that also hosts system_db, the other is a 64 bit Linux
> machine) . pgpool is also installed on the server32 machine.
> This is the configuration we use:**
> 
> * *# if non 0, run in parallel query mode*
> *parallel_mode = true*
> **
> * # if non 0, use query cache*
> *enable_query_cache = false*
> **
> *#set pgpool2 hostname *
> *pgpool2_hostname = 'localhost'*
> **
> *# system DB info*
> * system_db_hostname = 'localhost'*
> *system_db_port = 5432*
> *system_db_dbname = 'pgpool'*
> * system_db_schema = 'pgpool_catalog'*
> *system_db_user = 'pgpool'*
> *system_db_password = 'pgpool'*
> **
> *# backend_hostname, backend_port, backend_weight*
> *# here are examples*
> *backend_hostname0 = 'server32'*
> *backend_port0 = 5432*
> *backend_weight0 = 1*
> *
> *
> *backend_hostname1 = 'server64' *
> *backend_port1 = 5432*
> *backend_weight1 = 1*
> **
> *
> 
> **This is the content of our "pgpool_catalog.dist_def" table.**
> 
> * * dbname | schema_name | table_name | col_name |  col_list   |
> type_list    |       dist_def_func*
> *--------+-------------+------------+----------+-------------+-----------------+---------------------------
> *
> * pool   | pool        | users      | uid    | {uid,age} | {bigint,bigint} |
> pgpool_catalog.dist_users*
> **
> *
> 
> **The defined function is:**
> * *CREATE OR REPLACE FUNCTION pgpool_catalog.dist_users(val anyelement)*
> *  RETURNS integer AS*
> * $BODY$*
> *    SELECT CASE*
> *        WHEN ($1 % 1024) >= 0 and ($1 % 1024) < 512 THEN 0*
> *         ELSE 1*
> *END$BODY$*
> *  LANGUAGE 'sql' VOLATILE;*
> *ALTER FUNCTION pgpool_catalog.dist_users(val anyelement) OWNER TO pgpool; *
> **
> *
> 
> **I hope someone here could provide me some guidance (if you need more info,
> let me know).
> 
> Thanks
>    Tzvi.
> *
> 

-- 
SRA OSS, Inc. Japan
Yoshiharu Mori <y-mori at sraoss.co.jp>
http://www.sraoss.co.jp/