[pgcluster: 869] テーブルの登録等でリクエストが重複
柳田
yanagita.tomohiko ＠ itk.nifty.co.jp
2006年 4月 5日 (水) 17:56:47 JST
はじめて投稿させていただきます。柳田と申します。
pgcluster-1[1].0.9a.tar.gz を使用しております。
クラスタDBが３台。そのクラスタ３台に対する、レプリケションサーバー１台という構成です。
レプリケーションサーバーはクラスタサーバー１台と同居しています。
ご相談したい事象ですが、クラスタDBに対し,テーブルの登録やレコードの追加を行った際、実行したクラスタDBで
リクエストが３回発行されてしまいます。作成したテーブルにはプライマリキーを設定しているのでKEY重複でエラーとなります。
設定ファイルのケアレスミスのような気もしているのですが、どうにも問題が解決できません。
以下に【OS】、【cluster.conf】、【pgreplicate.conf 】、【再現方法】、【再現中のレプリケーションデバッグ】を記載しました。
よろしくお願いします。
【OS】----------------------------------------
すべて下記のOSです。
Red Hat Enterprise Linux ES release 3 (Taroon)
【cluster.conf】３台のクラスタDBは同じ設定--------------
# set Replication Server information
<Replicate_Server_Info>
<Host_Name> happy114 </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
# set Cluster DB Server information
<Recovery_Port> 7001 </Recovery_Port>
<Rsync_Path> /usr/bin/rsync </Rsync_Path>
<Rsync_Option> ssh -1 </Rsync_Option>
<When_Stand_Alone> read_only </When_Stand_Alone>
【pgreplicate.conf 】--------------------------------
# A setup of Cluster DB(s)
<Cluster_Server_Info>
<Host_Name> happy118 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> happy119 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> happy114 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
# A setup of Load Balance Server
<LoadBalance_Server_Info>
<Host_Name> happy119 </Host_Name>
<Recovery_Port> 7780 </Recovery_Port>
</LoadBalance_Server_Info>
# A setup of a replication server
<Replication_Port> 8001 </Replication_Port>
<Recovery_Port> 8101 </Recovery_Port>
<Response_Mode> reliable </Response_Mode>
【再現方法】--------------------------------------------------------------------
$ createdb -E EUC_JP hogedb <--●●●DB作成●●●
ERROR: CREATE DATABASE: source database "template1" is being accessed by other users
CREATE DATABASE
$ psql hogedb
Welcome to psql 7.3.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
hogedb=# CREATE TABLE t_hoge <--●●●テーブル作成●●●
hogedb-# (
hogedb(# hoge_id varchar(20) NOT NULL,
hogedb(# hoge_no int4 DEFAULT 0,
hogedb(# PRIMARY KEY ( hoge_id )
hogedb(# )
hogedb-# ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_hoge_pkey' for table 't_hoge'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_hoge_pkey' for table 't_hoge'
ERROR: Relation 't_hoge' already exists
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_hoge_pkey' for table 't_hoge'
ERROR: Relation 't_hoge' already exists
hogedb=# insert into t_hoge(hoge_id,hoge_no) values ('AAA','1'); <--●●●レコード登録●●●
ERROR: Cannot insert a duplicate key into unique index t_hoge_pkey
ERROR: Cannot insert a duplicate key into unique index t_hoge_pkey
hogedb=#
【再現中のレプリケーションデバッグ】-------------------------------------------------
DEBUG:PGRdo_replicate():query :: CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:cmdSts=Q
DEBUG:cmdType=O
DEBUG:port=5432
DEBUG:pid=30545
DEBUG:from_host=happy118.localdomain
DEBUG:dbName=template1
DEBUG:userName=postgres
DEBUG:recieve sec=1144223446
DEBUG:recieve usec=660371
DEBUG:query_size=49
DEBUG:query=CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(happy118) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[happy118] port[5432] db[template1] user[postgres]
DEBUG:PGRsend_replicate_packet_to_server():host(happy119) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[happy119] port[5432] db[template1] user[postgres]
DEBUG:PGRsend_replicate_packet_to_server():host(happy114) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[happy114] port[5432] db[template1] user[postgres]
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:template1 port:5432 user:postgres host:happy114 query:CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223446,660371,2) )
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:template1 port:5432 user:postgres host:happy118 query:CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223446,660371,2) )
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:template1 port:5432 user:postgres host:happy119 query:CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223446,660371,2) )
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :CREATE DATABASE "hogedb" WITH ENCODING = 'EUC_JP'
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[6]
DEBUG:PGRreturn_result():128 send
DEBUG:replicate_loop():replicate loop exit
DEBUG:PGRdo_replicate():query :: CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
DEBUG:cmdSts=Q
DEBUG:cmdType=O
DEBUG:port=5432
DEBUG:pid=30605
DEBUG:from_host=happy118.localdomain
DEBUG:dbName=hogedb
DEBUG:userName=postgres
DEBUG:recieve sec=1144223548
DEBUG:recieve usec=290794
DEBUG:query_size=138
DEBUG:query=CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(happy118) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[happy118] port[5432] db[hogedb] user[postgres]
DEBUG:PGRsend_replicate_packet_to_server():host(happy119) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[happy119] port[5432] db[hogedb] user[postgres]
DEBUG:PGRsend_replicate_packet_to_server():host(happy114) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[happy114] port[5432] db[hogedb] user[postgres]
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:hogedb port:5432 user:postgres host:happy114 query:CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223548,290794,2) )
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_hoge_pkey' for table 't_hoge'
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:hogedb port:5432 user:postgres host:happy118 query:CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223548,290794,2) )
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:hogedb port:5432 user:postgres host:happy119 query:CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223548,290794,2) )
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_hoge_pkey' for table 't_hoge'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_hoge_pkey' for table 't_hoge'
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :CREATE TABLE t_hoge
(
hoge_id varchar(20) NOT NULL,
hoge_no int4 DEFAULT 0,
PRIMARY KEY ( hoge_id )
)
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[6]
DEBUG:PGRreturn_result():128 send
DEBUG:PGRdo_replicate():query :: insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:cmdSts=Q
DEBUG:cmdType=I
DEBUG:port=5432
DEBUG:pid=30605
DEBUG:from_host=happy118.localdomain
DEBUG:dbName=hogedb
DEBUG:userName=postgres
DEBUG:recieve sec=1144223635
DEBUG:recieve usec=155384
DEBUG:query_size=54
DEBUG:query=insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(happy118) : port(5432)
DEBUG:PGRsend_replicate_packet_to_server():connect db:hogedb port:5432 user:postgres host:happy118 query:insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223635,155384,2) )
DEBUG:PGRsend_replicate_packet_to_server():host(happy119) : port(5432)
DEBUG:PGRsend_replicate_packet_to_server():connect db:hogedb port:5432 user:postgres host:happy119 query:insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223635,155384,2) )
DEBUG:PGRsend_replicate_packet_to_server():host(happy114) : port(5432)
DEBUG:PGRsend_replicate_packet_to_server():connect db:hogedb port:5432 user:postgres host:happy114 query:insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1144223635,155384,2) )
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :insert into t_hoge(hoge_id,hoge_no) values ('AAA','1')
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[6]
DEBUG:PGRreturn_result():128 send
pgcluster メーリングリストの案内