[pgsql-jp: 34488] Re: pgpool 2.3.3 で強制的に縮退運転

2004年 12月 15日 (水) 16:07:42 JST

石井です．

> 度々お世話になっております、金戸と申します。
> 
> Red Hat Enterprise Linux ES release 3 (Taroon)
> postgres (PostgreSQL) 7.4.5
> pgpool version 2.3.3(iheihe)
> にて

まず状況を確認させていただきたいのですが，psqlのバージョンが7.4.5では
ないようですが，そういう理解であっていますか？
--
Tatsuo Ishii

> 間違ってキー重複データを作成してコマンドラインから\copy したところ
> replication_stop_on_mismatch = false
> にもかかわらず、下記１の状態になり縮退運転に入ってしまいます。
> 
> psql上のcopy で再現させようと type error を出してみたところ
> 下記２の状態になり、やはり縮退運転に入ってしまいます。
> ちなみに replication_stop_on_mismatch = true でも動作は同じです。
> 
> 以前 pgAdmin3forWin でメンテナンス中に mismatch が発生したことがあります。
> データを削除したとき、片方のＤＢからだけ削除されるというものでした。
> #pgAdmin3 も copy も oid で指定できるところが共通点かなと思いますが・・
> 
> ～～～１～～～
> １項目目がキーになっているテーブルにt0001.csv
> で psql -f recover.txt HOGE hog した場合
> 
> ～ t0001.csv（tab 区切り） ～
> 1AMLlB 02 01 test10 test10 テストユーザ 000 1100433 2983
> 1AMLlB 02 01 test11 test11 テストユーザ 000 1100433 2983
> 　　↑キー
> 
> ～ recover.txt ～
> delete from t0001;
> \copy t0001 from 't0001.csv'
> 
> ～ コンソールのエラー ～
> DELETE 0
> psql:recover.txt:2: ERROR:  duplicate key violates unique constraint "uqt000110"
> psql:recover.txt:2: lost synchronization with server, resetting connection
> 
> ～ ログ ～
> DEBUG: pid 11075: I am 11075 accept fd 6
> DEBUG: pid 11075: Protocol Major: 2 Minor: 0 database: HOGE user: hog
> DEBUG: pid 11075: read kind from backend pending data Z len: 0 po: 0
> DEBUG: pid 11075: read kind from frontend Q(51)
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11075: Query: begin; select getdatabaseencoding(); commit
> DEBUG: pid 11075: read kind from backend C
> DEBUG: pid 11075: pool_process_query: waiting for secondary for data ready
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:6 len:69
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:6 len:69
> DEBUG: pid 11075: Complete Command Response: string: "BEGIN"
> DEBUG: pid 11075: read kind from backend pending data P len: 68 po: 7
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:13 len:62
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:13 len:62
> DEBUG: pid 11075: read kind from backend pending data T len: 61 po: 14
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:36 len:39
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:36 len:39
> DEBUG: pid 11075: RowDescription: field size:64
> DEBUG: pid 11075: read kind from backend pending data D len: 28 po: 47
> DEBUG: pid 11075: AsciiRow: len: 6 data: EUC_JP
> DEBUG: pid 11075: read kind from backend pending data C len: 16 po: 59
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:66 len:9
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:66 len:9
> DEBUG: pid 11075: Complete Command Response: string: "SELECT"
> DEBUG: pid 11075: read kind from backend pending data C len: 8 po: 67
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:74 len:1
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:74 len:1
> DEBUG: pid 11075: Complete Command Response: string: "COMMIT"
> DEBUG: pid 11075: read kind from backend pending data Z len: 0 po: 0
> DEBUG: pid 11075: read kind from frontend Q(51)
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11075: Query: delete from t0001;
> DEBUG: pid 11075: read kind from backend P
> DEBUG: pid 11075: pool_process_query: waiting for secondary for data ready
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:6 len:11
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:6 len:11
> DEBUG: pid 11075: read kind from backend pending data C len: 10 po: 7
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:16 len:1
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:16 len:1
> DEBUG: pid 11075: Complete Command Response: string: "DELETE 0"
> DEBUG: pid 11075: read kind from backend pending data Z len: 0 po: 0
> DEBUG: pid 11075: read kind from frontend Q(51)
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11075: Query: COPY t0001 FROM STDIN
> DEBUG: pid 11075: read kind from backend G
> DEBUG: pid 11075: pool_process_query: waiting for secondary for data ready
> DEBUG: pid 11075: pool_read_string: total result 86 with pending data po:0 len:164
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:86 len:78
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:161 len:3
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11075: read kind from backend E
> DEBUG: pid 11075: pool_process_query: waiting for secondary for data ready
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11075: read kind from backend Z
> DEBUG: pid 11075: pool_process_query: waiting for secondary for data ready
> DEBUG: pid 11075: read kind from frontend X(58)
> DEBUG: pid 11075: Query: ABORT
> DEBUG: pid 11050: I am 11050 accept fd 6
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11075: pool_read_string: read all from pending data. po:0 len:0DEBUG: pid 11050: Protocol
> Major: 2Minor: 0 database: HOGE user: hog
> DEBUG: pid 11050: no empty connection slot was found
> DEBUG: pid 11050: user: sho database: HOGE closetime: 1103073477
> ERROR: pid 11075: pool_read: EOF encountered
> DEBUG: pid 11050: discarding old 0 th connection. user: hog database: HOGELOG: pid 11075:
> notice_backend_error: master: 1 fail over request from pid 11075
> DEBUG: pid 11046: failover_handler called
> LOG: pid 11046: starting degeneration. shutdown master host mastdb.xx.co.jp(5433)
> DEBUG: pid 11046: kill 11047
> DEBUG: pid 11046: kill 11048
> DEBUG: pid 11046: kill 11049
> DEBUG: pid 11046: kill 11050
> DEBUG: pid 11046: kill 11075
> DEBUG: pid 11046: kill 11052
> DEBUG: pid 11084: I am 11084
> DEBUG: pid 11085: I am 11085
> DEBUG: pid 11086: I am 11086
> DEBUG: pid 11087: I am 11087
> DEBUG: pid 11088: I am 11088
> DEBUG: pid 11089: I am 11089
> LOG: pid 11046: degeneration done. shutdown master host mastdb.xx.co.jp(5433)
> DEBUG: pid 11046: reap_handler called
> 
> ～～～２～～～
> copy で標準入力から異常データをセットした場合
> $ psql -U hog HOGE
> 
> HOGE=> copy t0001 from stdin;
> Enter data to be copied followed by a newline.
> End with a backslash and a period on a line by itself.
> >> 'hogehogehoge'<実際にこの通り入力しました
> >> \.
> ERROR:  value too long for type character varying(2)
> lost synchronization with server, resetting connection
> The connection to the server was lost. Attempting reset: Succeeded.
> 
> ～ ログ ～
> DEBUG: pid 11446: read kind from frontend Q(51)
> DEBUG: pid 11446: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11446: Query: copy t0001 from stdin;
> DEBUG: pid 11446: read kind from backend G
> DEBUG: pid 11446: pool_process_query: waiting for secondary for data ready
> 
> DEBUG: pid 11446: pool_read_string: total result 15 with pending data po:0 len:3
> DEBUG: pid 11446: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11446: read kind from backend E
> DEBUG: pid 11446: pool_process_query: waiting for secondary for data ready
> DEBUG: pid 11446: pool_read_string: read all from pending data. po:54 len:44
> DEBUG: pid 11446: pool_read_string: read all from pending data. po:54 len:44
> DEBUG: pid 11446: read kind from backend pending data Z len: 43 po: 55
> DEBUG: pid 11446: read kind from frontend X(58)
> DEBUG: pid 11446: Query: ABORT
> DEBUG: pid 11446: read kind from backend pending data E len: 42 po: 56
> DEBUG: pid 11446: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11446: pool_read_string: read all from pending data. po:0 len:0
> ERROR: pid 11446: pool_read: EOF encountered
> LOG: pid 11446: notice_backend_error: master: 1 fail over request from pid 11446
> DEBUG: pid 11441: failover_handler called
> LOG: pid 11441: starting degeneration. shutdown master host mastdb.xx.co.jp(5433)
> DEBUG: pid 11441: kill 11442
> DEBUG: pid 11441: kill 11443
> DEBUG: pid 11441: kill 11444
> DEBUG: pid 11441: kill 11445
> DEBUG: pid 11441: kill 11446
> DEBUG: pid 11441: kill 11447
> DEBUG: pid 11453: I am 11453
> DEBUG: pid 11453: I am 11453 accept fd 6
> DEBUG: pid 11453: Protocol Major: 2 Minor: 0 database: HOGE user: hog
> DEBUG: pid 11454: I am 11454
> DEBUG: pid 11455: I am 11455
> DEBUG: pid 11456: I am 11456
> DEBUG: pid 11457: I am 11457
> DEBUG: pid 11458: I am 11458
> LOG: pid 11441: degeneration done. shutdown master host mastdb.xx.co.jp(5433)
> DEBUG: pid 11441: reap_handler called
> DEBUG: pid 11453: read kind from backend pending data Z len: 0 po: 0
> DEBUG: pid 11453: read kind from frontend Q(51)
> DEBUG: pid 11453: pool_read_string: read all from pending data. po:0 len:0
> DEBUG: pid 11453: Query: begin; select getdatabaseencoding(); commit
> DEBUG: pid 11453: read kind from backend C
> DEBUG: pid 11453: pool_read_string: read all from pending data. po:6 len:69
> DEBUG: pid 11453: Complete Command Response: string: "BEGIN"
> DEBUG: pid 11453: read kind from backend pending data P len: 68 po: 7
> DEBUG: pid 11453: pool_read_string: read all from pending data. po:13 len:62
> DEBUG: pid 11453: read kind from backend pending data T len: 61 po: 14
> DEBUG: pid 11453: pool_read_string: read all from pending data. po:36 len:39
> DEBUG: pid 11453: RowDescription: field size:64
> DEBUG: pid 11453: read kind from backend pending data D len: 28 po: 47
> DEBUG: pid 11453: AsciiRow: len: 6 data: EUC_JP
> DEBUG: pid 11453: read kind from backend pending data C len: 16 po: 59
> DEBUG: pid 11453: pool_read_string: read all from pending data. po:66 len:9
> DEBUG: pid 11453: Complete Command Response: string: "SELECT"
> DEBUG: pid 11453: read kind from backend pending data C len: 8 po: 67
> DEBUG: pid 11453: pool_read_string: read all from pending data. po:74 len:1
> DEBUG: pid 11453: Complete Command Response: string: "COMMIT"
> DEBUG: pid 11453: read kind from backend pending data Z len: 0 po: 0
> 
>