The following bug has been logged online:
Bug reference: 3086
Logged by: Akio Iwaasa
Email address: iwaasa@mxs.nes.nec.co.jp
PostgreSQL version: 8.1.8
Operating system: Redhat EL ES 3.0
Description: DBMirror's error (SERIAL attribute)
Details:
I'm sorry for my poor English.
An error occured in DBMirror when I executed INSERT
to the table which had SERIAL attribute.
"DBMirror" returned the following messages.
$ psql -c "INSERT INTO seqtest1 (C2) values (1) ;" db1
INSERT 0 1
$ Error sending query 5968 to localhost
select setval('seqtest1_c1_seq',1,'t') at
/usr/local/pgsql818/bin/DBMirror.pl line 771.
[1] Problem on DBMirror.pl
I found a problem on sendQueryToSlaves(DBMirror.pl).
When "PGRES_TUPLES_OK" was returned from PostgreSQL,
DBMirror returns error message.
[2] Environment of DBMirror
Master DB : db1 (localhost)
Slave DB : db2 (localhost,slave1)
[2-1] Master DB setup script(psql)
--
-- Setup Master DB
--
\i /usr/local/pgsql/share/contrib/MirrorSetup.sql
INSERT INTO dbmirror_MirrorHost (SlaveName) VALUES('slave1') ;
--
-- Define Table
--
CREATE TABLE seqtest1 (c1 SERIAL,c2 INT) ;
--
-- AddTrigger.sql
--
CREATE TRIGGER "seqtest1_Trig"
AFTER INSERT OR DELETE OR UPDATE ON "seqtest1"
FOR EACH ROW EXECUTE PROCEDURE "recordchange" ();
[2-2] Slave DB setup script(psql)
--
-- Setup Slave DB
--
\i /usr/local/pgsql/share/contrib/MirrorSetup.sql
--
-- Define Table
--
CREATE TABLE seqtest1 (c1 SERIAL,c2 INT) ;
[3] Patch
I evaded the trouble by following patche.
----- PATCH -----
*** DBMirror.pl 2007-02-28 10:41:10.000000000 +0900
--- DBMirror.pl.new 2007-02-28 10:44:13.000000000 +0900
***************
*** 696,702 ****
if($::slaveInfo->{"status"} eq 'DBOpen') {
my $queryResult = $::slaveInfo->{"slaveConn"}->exec($sqlQuery);
! unless($queryResult->resultStatus == PGRES_COMMAND_OK) {
my $errorMessage;
$errorMessage = "Error sending query $seqId to " ;
$errorMessage .= $::slaveInfo->{"slaveHost"};
--- 696,703 ----
if($::slaveInfo->{"status"} eq 'DBOpen') {
my $queryResult = $::slaveInfo->{"slaveConn"}->exec($sqlQuery);
! unless($queryResult->resultStatus == PGRES_COMMAND_OK ||
! $queryResult->resultStatus == PGRES_TUPLES_OK ) {
my $errorMessage;
$errorMessage = "Error sending query $seqId to " ;
$errorMessage .= $::slaveInfo->{"slaveHost"};
----- PATCH -----
Regards.