[pgpool-general-jp: 272] Re: pgpool-II パラレルモード使用下で特定の処理を行うと停止する

Yoshiharu Mori y-mori @ sraoss.co.jp
2007年 11月 5日 (月) 13:34:07 JST


浅野様

盛です。

> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_one_backend: read kind
> from 1 th backend Z
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy kind from
> backend: Z

読んではいけないファイルディスクリプタからデータを読み込んでいるようです。
以下のパッチをpool_process_query.cに当てるといかがでしょうか?

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgpool/pgpool-II/pool_process_query.c.diff?r1=1.23.2.24&r2=1.23.2.25&f=u


> お世話になります。浅野と申します。
> 
> 
> 当方にて制作しているWEBアプリケーションで問題が発生しております。
> 
> pgpool-II (パラレルクエリモード) 使用下で、
> 特定のPHPプログラムファイルを実行すると
> pgpool-IIが停止してしまうようです。
> 
> どなたか同じ状況が発生した方が見えましたら、
> 対処法をご教示頂ければ幸いです。
> 
> 宜しくお願い申し上げます。
> 
> 
> [状況]
>     ・トピックを作成し、そのそれぞれのトピックについてコメントを書き込める
>      (スレッド式掲示板に近いと思います)
> 
>     ・ページを閲覧に行くと延々と「読み込み中」の状態が続き、
>      ページが表示できない。
> 
>     ・pgpool-IIとPostgreSQLを再起動させると数回の閲覧は可能だが、
>      更新を繰り返したり、サイト内の別のページを閲覧した後に見に来ると停止状態になる。
> 
>     ・サイト内に他に同じような処理をさせるPHPプログラムがあるが、そちらでは
>       この現象は発生しない。
> 
>     ・pgpool-II、PostgreSQLのログの両者共それらしい出力は見当たらない模様。
>      というより、「読み込み中」状態になるとログに何も出力されてこない。
> 
>         tail -f -n20 /usr/local/pgsql/data.1/pg_log/postgresql.log
>         tail -f -n20 /usr/local/pgpool/log/pgpool.log
> 
>      上記コマンドで確認したが、何も出力されない。
>      さらに待っているとpostgresqlのログにautovacuumの処理は出力されてくる。
> 
>     ・pgpool-IIを停止させて、単体のPostgreSQLで動かしたところ、この現象は発生しなかった。
> 
>     ・pgpool-IIのバージョン1.3でも1.2でも停止状態になった。
> 
> 
> -----------------------------------------------------------
> データベースは実機一台にポートで分けて三つを起動させている状態。
> [PostgreSQL起動時コマンド]
> PGPORT=5432 pg_ctl -D /usr/local/pgsql/data start
> PGPORT=5433 pg_ctl -D /usr/local/pgsql/data.1 start
> PGPORT=5434 pg_ctl -D /usr/local/pgsql/data.2 start
> 
> [pgpool起動時コマンド]
> /usr/local/pgpool/bin/pgpool -n -d >& /usr/local/pgpool/log/pgpool.log 2>&1
> &
> 
> -----------------------------------------------------------
> [ pgpool-II設定ファイル pgpool.conf ]
> 
> listen_addresses = 'localhost'
> port = 9999
> pcp_port = 9898
> socket_dir = '/tmp'
> pcp_socket_dir = '/tmp'
> backend_socket_dir = '/tmp'
> pcp_timeout = 10
> num_init_children = 32
> max_pool = 4
> child_life_time = 300
> connection_life_time = 0
> child_max_connections = 0
> logdir = '/tmp'
> replication_mode = false
> replication_strict = true
> replication_timeout = 5000
> load_balance_mode = false
> replication_stop_on_mismatch = false
> reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
> print_timestamp = true
> master_slave_mode = false
> connection_cache = true
> health_check_timeout = 20
> health_check_period = 0
> health_check_user = 'nobody'
> insert_lock = false
> ignore_leading_white_space = false
> log_statement = false
> log_connections = false
> log_hostname = false
> parallel_mode = true
> enable_query_cache = false
> pgpool2_hostname = 'localhost'
> system_db_hostname = 'localhost'
> system_db_port = 5432
> system_db_dbname = 'pgpool'
> system_db_schema = 'pgpool_catalog'
> system_db_user = 'postgres'
> system_db_password = ''
> 
> backend_hostname0 = ''
> backend_port0 = 5432
> backend_weight0 = 1
> 
> backend_hostname1 = ''
> backend_port1 = 5433
> backend_weight1 = 1
> 
> backend_hostname2 = ''
> backend_port2 = 5434
> backend_weight2 = 1
> enable_pool_hba = false
> 
> -----------------------------------------------------------
> 【 関連するテーブル 】
> 
> [テーブル名:c_commu_topic]
>  c_commu_topic_id  | integer                     | not null
>  c_commu_id        | integer                     | not null default 0
>  name              | text                        | not null
>  r_datetime        | timestamp without time zone | not null default now()
>  r_date            | date                        | not null default
> ('now'::text)::date
>  c_member_id       | integer                     | not null default 0
>  open_date         | date                        | not null default
> ('now'::text)::date
>  open_date_comment | character varying(100)      | not null
>  open_pref_id      | integer                     | not null default 0
>  open_pref_comment | character varying(100)      | not null
>  invite_period     | date                        | not null default
> ('now'::text)::date
>  event_flag        | integer                     | not null default 0
> 
> 
> [テーブル名:c_commu_topic_comment]
>  c_commu_topic_comment_id | integer                     | not null
>  c_commu_id               | integer                     | not null default 0
>  c_member_id              | integer                     | not null default 0
>  body                     | text                        | not null
>  r_datetime               | timestamp without time zone | not null default
> now()
>  r_date                   | date                        | not null default
> ('now'::text)::date
>  number                   | integer                     | not null default 0
>  c_commu_topic_id         | integer                     | not null default 0
>  image_filename1          | character varying(200)      | not null
>  image_filename2          | character varying(200)      | not null
>  image_filename3          | character varying(200)      | not null
>  image_width1             | smallint                    | not null default 0
>  image_height1            | smallint                    | not null default 0
>  image_width2             | smallint                    | not null default 0
>  image_height2            | smallint                    | not null default 0
>  image_width3             | smallint                    | not null default 0
>  image_height3            | smallint                    | not null default 0
> 
> 
> -----------------------------------------------------------
> 【分割ルール設定】 (pgpool_catalog.dist_def )
> 
> [c_commu_topic テーブル用]
> insert into pgpool_catalog.dist_def values(
> 'topic_main_db',
> 'public',
> 'c_commu_topic',
> 'c_commu_topic_id',
> ARRAY['c_commu_topic_id','c_commu_id','name','r_datetime','r_date','c_member_id','open_date','open_date_comment','open_pref_id','open_pref_comment','invite_period','event_flag'],
> ARRAY['INTEGER','INTEGER','TEXT','TIMESTAMP','DATE','INTEGER','DATE','CHARACTER
> VARYING(100)','INTEGER','CHARACTER VARYING(100)','DATE','INTEGER'],
> 'pgpool_catalog.dist_def_c_commu_topic')
> 
> 
> [c_commu_topic_comment テーブル用]
> insert into pgpool_catalog.dist_def values(
> 'topic_main_db',
> 'public',
> 'c_commu_topic_comment',
> 'c_commu_topic_comment_id',
> ARRAY['c_commu_topic_comment_id','c_commu_id','c_member_id','body','r_datetime','r_date','number','c_commu_topic_id','image_filename1','image_filename2','image_filename3','image_width1','image_height1','image_width2','image_height2','image_width3','image_height3'],
> ARRAY['INTEGER','INTEGER','INTEGER','TEXT','TIMESTAMP','DATE','INTEGER','INTEGER','CHARACTER
> VARYING(200)','CHARACTER VARYING(200)','CHARACTER
> VARYING(200)','SMALLINT','SMALLINT','SMALLINT','SMALLINT','SMALLINT','SMALLINT'],
> 'pgpool_catalog.dist_def_c_commu_topic_comment')
> 
> -----------------------------------------------------------
> 【分割ルールファンクション】
> 
> [c_commu_topic テーブル用]
> CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_c_commu_topic(val
> ANYELEMENT) RETURNS INTEGER AS $$
> SELECT CASE WHEN $1 > 0 then 1
> END $$ LANGUAGE SQL;
> 
> [c_commu_topic_comment テーブル用]
> CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_c_commu_topic_comment(val
> ANYELEMENT) RETURNS INTEGER AS $$
> SELECT CASE WHEN $1 > 0 then 1
> END $$ LANGUAGE SQL;
> 
> -----------------------------------------------------------
> 【 pgpool 停止した直前のログ 】 (※ユーザ名は■■■で置き換えてます。 )
> 
> 2007-11-02 21:33:59 DEBUG: pid 26766: 0 th backend: name:
> session_authorization value: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26766: 1 th backend: name:
> session_authorization value: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26766: 2 th backend: name:
> session_authorization value: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
> 0 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
> 1 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
> 2 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_process_query: kind from backend:
> C
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
> 0 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
> 1 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
> 2 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_process_query: kind from backend:
> Z
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_read_message_length: slot: 0
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_read_message_length: slot: 1
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_read_message_length: slot: 2
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26766: ReadyForQuery: message length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26766: ReadyForQuery: transaction state: I
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_connection_pool_timer: set close
> time 1194006839
> 2007-11-02 21:33:59 DEBUG: pid 26863: Protocol Major: 3 Minor: 0 database:
> topic_main_db user: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_send_auth_ok: send pid 26893 to
> frontend
> 2007-11-02 21:33:59 DEBUG: pid 26863: select_load_balancing_node: selected
> backend id is 0
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: num_fds: 13
> 2007-11-02 21:33:59 DEBUG: pid 26863: read kind from frontend Q(51)
> 2007-11-02 21:33:59 DEBUG: pid 26863: statement2: SELECT
> pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
> ='6'")
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_query: num_fds: 13
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_one_backend: read kind
> from 0 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> T
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy from
> backend: C
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_one_backend: read kind
> from 1 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy kind from
> backend: T
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy from
> backend: C
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_one_backend: read kind
> from 2 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy kind from
> backend: T
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
> C
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 0 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 1 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 2 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
> Z
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 0
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 1
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 2
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: message length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: transaction state: I
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: num_fds: 13
> 2007-11-02 21:33:59 DEBUG: pid 26863: read kind from frontend X(58)
> 2007-11-02 21:33:59 DEBUG: pid 26863: statement2:  RESET ALL
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_rewrite_stmt: XXX rule 743
> 2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 0 completing the
> query
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_rewrite_stmt: XXX message_code 7
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_rewrite_stmt: XXX rule 704
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_process_query: num_fds: 13
> 2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 1 completing the
> query
> 2007-11-02 21:33:59 DEBUG: pid 26837: read kind from frontend Q(51)
> 2007-11-02 21:33:59 DEBUG: pid 26837: statement2: select * from
> c_commu_topic_comment where c_commu_topic_id='3'
> 2007-11-02 21:33:59 DEBUG: pid 26837: can pool_parallel_exec  SELECT "*"
> FROM "c_commu_topic_comment" WHERE  ("c_commu_topic_id"='3' )
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_query: num_fds: 13
> 2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_one_backend: read kind
> from 0 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
> T
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy from
> backend: C
> 2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_one_backend: read kind
> from 2 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy kind from
> backend: T
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy from
> backend: C
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_query: num_fds: 13
> 2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 2 completing the
> query
> 2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_one_backend: read kind
> from 1 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy kind from
> backend: T
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
> C
> 2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_backend: read kind from
> 0 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_backend: read kind from
> 1 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_backend: read kind from
> 2 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_process_query: kind from backend:
> Z
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_read_message_length: slot: 0
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_read_message_length: slot: 1
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_read_message_length: slot: 2
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26837: ReadyForQuery: message length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26837: ReadyForQuery: transaction state: I
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 0 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 1 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 2 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
> C
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 0 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 1 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 2 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
> Z
> 2007-11-02 21:33:59 DEBUG: pid 26837: pool_process_query: num_fds: 13
> 2007-11-02 21:33:59 DEBUG: pid 26837: read kind from frontend Q(51)
> 2007-11-02 21:33:59 DEBUG: pid 26837: statement2: select * from
> c_commu_topic_comment where c_commu_topic_id='3' order by
> c_commu_topic_comment_id asc limit 1
> 2007-11-02 21:33:59 DEBUG: pid 26837: OneNode_do_command: Query:  SELECT *
> FROM dblink('host=localhost port=9999 dbname=topic_main_db ','SELECT
> pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
> =''3''")') AS c_commu_topic_comment(c_commu_topic_comment_id
> INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
> TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
> INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
> VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
> SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
> SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
> c_commu_topic_comment_id  LIMIT 1
> 2007-11-02 21:33:59 DEBUG: pid 26766: I am 26766 accept fd 6
> 2007-11-02 21:33:59 DEBUG: pid 26766: Protocol Major: 1234 Minor: 5679
> database:  user:
> 2007-11-02 21:33:59 DEBUG: pid 26766: SSLRequest: sent N; retry startup
> 2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 1 completing the
> query
> 2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 2 completing the
> query
> 2007-11-02 21:33:59 DEBUG: pid 26766: Protocol Major: 3 Minor: 0 database:
> topic_main_db user: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_send_auth_ok: send pid 26902 to
> frontend
> 2007-11-02 21:33:59 DEBUG: pid 26766: select_load_balancing_node: selected
> backend id is 1
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_process_query: num_fds: 10
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 0 th backend S NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 1 th backend S NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 2 th backend S NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
> S
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
> slot: 0 length: 21
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
> slot: 1 length: 21
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
> slot: 2 length: 21
> 2007-11-02 21:33:59 DEBUG: pid 26863: 0 th backend: name: is_superuser
> value: off
> 2007-11-02 21:33:59 DEBUG: pid 26863: 1 th backend: name: is_superuser
> value: off
> 2007-11-02 21:33:59 DEBUG: pid 26863: 2 th backend: name: is_superuser
> value: off
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 0 th backend S NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 1 th backend S NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 2 th backend S NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
> S
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
> slot: 0 length: 32
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
> slot: 1 length: 32
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
> slot: 2 length: 32
> 2007-11-02 21:33:59 DEBUG: pid 26863: 0 th backend: name:
> session_authorization value: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26863: 1 th backend: name:
> session_authorization value: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26863: 2 th backend: name:
> session_authorization value: ■■■
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 0 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 1 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 2 th backend C NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
> C
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 0 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 1 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
> 2 th backend Z NUM_BACKENDS: 3
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
> Z
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 0
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 1
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 2
> length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: message length: 5
> 2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: transaction state: I
> 2007-11-02 21:33:59 DEBUG: pid 26863: pool_connection_pool_timer: set close
> time 1194006839
> 2007-11-02 21:33:59 DEBUG: pid 26766: read kind from frontend Q(51)
> 2007-11-02 21:33:59 DEBUG: pid 26766: statement2: SELECT
> pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
> ='3'")
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_query: num_fds: 10
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_one_backend: read kind
> from 1 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: kind from backend:
> T
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: kind from backend:
> D
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy from
> backend: C
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_one_backend: read kind
> from 2 th backend T
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy kind from
> backend: T
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy from
> backend: C
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_query: num_fds: 10
> 2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_one_backend: read kind
> from 1 th backend Z
> 2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy kind from
> backend: Z
> 
> 
> 
> -----------------------------------------------------------
> 【 PostgreSQL 停止した直前のログ 】(※ユーザ名は■■■で置き換えてます。 )
> 
> 
> 2007-11-02 21:33:58 JST SETLOG:  00000: duration: 0.479 ms
> 2007-11-02 21:33:58 JST SETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SET SESSION
> AUTHORIZATION DEFAULT
> 2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:58 JST SETLOG:  00000: duration: 0.476 ms
> 2007-11-02 21:33:58 JST SETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SELECT *  FROM
> dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
> pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
> =''4''")') AS c_commu_topic_comment(c_commu_topic_comment_id
> INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
> TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
> INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
> VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
> SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
> SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
> c_commu_topic_comment_id  LIMIT 1
> 2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:58 JST idleLOG:  00000: statement: select * from
> c_commu_topic_comment WHERE c_commu_topic_id ='4'
> 2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:58 JST SELECTLOG:  00000: duration: 10.318 ms
> 2007-11-02 21:33:58 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:58 JST SELECTLOG:  00000: duration: 72.010 ms
> 2007-11-02 21:33:58 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SELECT "*" FROM
> "c_commu_topic_comment" WHERE  ("c_commu_topic_id"='5' )
> 2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:58 JST SELECTLOG:  00000: duration: 1.122 ms
> 2007-11-02 21:33:58 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:58 JST idleLOG:  00000: statement:  RESET ALL
> 2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:58 JST RESETLOG:  00000: duration: 0.355 ms
> 2007-11-02 21:33:58 JST RESETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SELECT *  FROM
> dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
> pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
> =''5''")') AS c_commu_topic_comment(c_commu_topic_comment_id
> INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
> TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
> INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
> VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
> SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
> SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
> c_commu_topic_comment_id  LIMIT 1
> 2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SET SESSION
> AUTHORIZATION DEFAULT
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SETLOG:  00000: duration: 0.525 ms
> 2007-11-02 21:33:59 JST SETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement: select * from
> c_commu_topic_comment WHERE c_commu_topic_id ='5'
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.098 ms
> 2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 58.556 ms
> 2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT "*" FROM
> "c_commu_topic_comment" WHERE  ("c_commu_topic_id"='6' )
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.120 ms
> 2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  RESET ALL
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST RESETLOG:  00000: duration: 0.383 ms
> 2007-11-02 21:33:59 JST RESETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT *  FROM
> dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
> pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
> =''6''")') AS c_commu_topic_comment(c_commu_topic_comment_id
> INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
> TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
> INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
> VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
> SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
> SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
> c_commu_topic_comment_id  LIMIT 1
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SET SESSION
> AUTHORIZATION DEFAULT
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SETLOG:  00000: duration: 0.508 ms
> 2007-11-02 21:33:59 JST SETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement: select * from
> c_commu_topic_comment WHERE c_commu_topic_id ='6'
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.117 ms
> 2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 43.969 ms
> 2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  RESET ALL
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST RESETLOG:  00000: duration: 0.371 ms
> 2007-11-02 21:33:59 JST RESETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT "*" FROM
> "c_commu_topic_comment" WHERE  ("c_commu_topic_id"='3' )
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.123 ms
> 2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SET SESSION
> AUTHORIZATION DEFAULT
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SETLOG:  00000: duration: 0.448 ms
> 2007-11-02 21:33:59 JST SETLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT *  FROM
> dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
> pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
> =''3''")') AS c_commu_topic_comment(c_commu_topic_comment_id
> INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
> TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
> INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
> VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
> SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
> SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
> c_commu_topic_comment_id  LIMIT 1
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST idleLOG:  00000: statement: select * from
> c_commu_topic_comment WHERE c_commu_topic_id ='3'
> 2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
> 2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.110 ms
> 2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
> 2007-11-02 21:34:40 JST LOG:  00000: autovacuum: processing database
> "template1"
> 2007-11-02 21:34:40 JST LOCATION:  AutoVacMain, autovacuum.c:412
> 
> -----------------------------------------------------------
> 【 PHPプログラム 】
> <?php
> 
> $c_member_id=19;
> 
> $commu_id = 3;
> 
> $resource_id = pg_connect("host=localhost port=9999 dbname=topic_main_db
> user=topic_main_user password=");
> 
> if(!$resource_id) {
>  print "<br><br>[ --CONNECTION ERROR-- ]<br><br>\n\n";
>  exit;
> }
> 
> //今日の日付
> $r_datetime = date("Y-m-d H:i:s");
> 
> $page_define=20;
> 
> $show_page=1;
> $line = $show_page * $page_define - $page_define;
> $line1 = $line + 1;
> $line2 = $line + $page_define;
> 
> //トピックを検索
> $sql3 = "select * from c_commu_topic where c_commu_id = '$commu_id' order by
> r_datetime desc limit $page_define offset $line";
> $res_sql3 = pg_query($resource_id,"$sql3");
> $list = "";
> while($arycol3 = pg_fetch_array($res_sql3)){
>  //そのトピックの書き込みの合計を取得
>  $sql = "select * from c_commu_topic_comment where
> c_commu_topic_id='$arycol3[c_commu_topic_id]'";
>  $res_sql = pg_query($resource_id,"$sql");
>  $total = pg_num_rows($res_sql);
>  $total = $total -1;
>  //そのトピックの最新の書き込み
>  $sql4 = "select * from c_commu_topic_comment where
> c_commu_topic_id='$arycol3[c_commu_topic_id]' order by
> c_commu_topic_comment_id asc limit 1";
>  $res_sql4 = pg_query($resource_id,"$sql4");
>  $arycol4 = pg_fetch_array($res_sql4);
>  $body = mb_strimwidth($arycol4['body'],0,300,"…",'EUC-JP');
>  $filename1 = $arycol4['image_filename1'];
>  $filename2 = $arycol4['image_filename2'];
>  $filename3 = $arycol4['image_filename3'];
>  $width1 = $arycol4['image_width1'];
>  $height1 = $arycol4['image_height1'];
>  $width2 = $arycol4['image_width2'];
>  $height2 = $arycol4['image_height2'];
>  $width3 = $arycol4['image_width3'];
>  $height3 = $arycol4['image_height3'];
> 
> 
> if($filename1 != ""){
>  $main_image = "<img src='image.php?image_id=$filename1' alt=''
> width='$base_width1' height='$base_height1' border='0' />";
> }else{
>  $main_image = "<img src='../common_img/spacer.gif' alt='' width='1'
> height='1' border='0'>";
> }
> 
>       $list .= "<tr>\n";
>       $list .= "  <td bgcolor='#CCCCCC'><table width='100%'  border='0'
> cellspacing='0' cellpadding='5'>\n";
>       $list .= "    <tr>\n";
>       $list .= "      <td width='30'>&nbsp;</td>\n";
>       $list .= "      <td width='120'>$arycol3[r_datetime]</td>\n";
>       $list .= "      <td><a
> href='topic_form.php?commu_id=$commu_id&topic_id=$arycol3[c_commu_topic_id]'>$arycol3[name]</a></td>\n";
>       $list .= "      <td width='30'>&nbsp;</td>\n";
>    $list .= "    </tr>\n";
>       $list .= "  </table></td>\n";
>       $list .= "</tr>\n";
>       $list .= "<tr>\n";
>       $list .= "  <td bgcolor='#FFFFFF'><table width='100%'  border='0'
> cellspacing='0' cellpadding='0'>\n";
>       $list .= "    <tr>\n";
>       $list .= "      <td>&nbsp;</td>\n";
>       $list .= "    </tr>\n";
>    $list .= "  </table>\n";
>   $list .= "<table width='100%'  border='0' cellspacing='0'
> cellpadding='5'>\n";
>   $list .= "  <tr>\n";
>   $list .= "  <td width='30' rowspan='2'>&nbsp;</td>\n";
>   $list .= "  <td valign='top'>$body</td>\n";
>   $list .= "  <td>$main_image</td>\n";
>   $list .= "  <td width='30' rowspan='2'>&nbsp;</td>\n";
>   $list .= "  </tr>\n";
>   $list .= "  <tr>\n";
>   $list .= "  <td colspan='2' align='right'>\n";
>   $list .= " <a
> href='topic_form.php?commu_id=$commu_id&topic_id=$arycol3[c_commu_topic_id]#write'
> class='font4'>書き込み($total)</a>";
>   $list .= "</td>\n";
>   $list .= " </tr>\n";
>   $list .= "</table>\n";
>       $list .= "   </td>\n";
>       $list .= "</tr>\n";
> }
> 
> if($list == ""){
>       $list .= "<tr>\n";
>       $list .= "  <td bgcolor='#FFFFFF'>\n";
>       $list .= "    <tr>\n";
>       $list .= "      <td align='center'>記事がありません</td>\n";
>       $list .= "    </tr>\n";
>       $list .= "  </td>\n";
>       $list .= "</tr>\n";
> }
> 
> ?>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
> "http://www.w3.org/TR/html4/loose.dtd">
> <html>
> <head>
> <meta http-equiv="Content-Type" content="text/html; charset=euc-jp">
> <title>タイトル</title>
> </head>
> 
> <body bgcolor="#FFFFFF" leftmargin="0" topmargin="0">
> <table>
> <?=$list ?>
> </table>
> </body>
> </html>
> 
> -----------------------------------------------------------
> OS:      Cent OS 4.4
> Apache:    httpd-2.0.52
> PHP:       4.4.5
> pgpool-II:     1.3
> PostgreSQL  8.1.4
> 
> ------------------
> エニグマ   浅野
> 
> asano @ engm.com
> 
> 
> _______________________________________________
> pgpool-general-jp mailing list
> pgpool-general-jp @ sraoss.jp
> http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp
> 


-- 
SRA OSS, Inc. 日本支社
Yoshiharu Mori <y-mori @ sraoss.co.jp>
http://www.sraoss.co.jp/


pgpool-general-jp メーリングリストの案内