Oops. It appears that this is a known problem with 3.0 and 3.0.1:
http://lists.pgfoundry.org/pipermail/pgpool-general/2010-October/002995.html
The fix is already in CVS HEAD. Please grab snapshot file. Or apply included
patches.
BTW, pgpool-II 2.3.3 does not have this functionality in the first place...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> I couldn't reproduce your problem by using pgpool-II 3.1(CVS HEAD).
> Following is the test case.
>
> create table t1(i int); -- via pgpool
> insert into t1 values(1); -- via pgpool
>
> delete from t1; -- execute on 1th DB node
>
> update t1 set i = 10; -- via pgpool
>
> pgpool detected the error and did failover.
>
> 2010-11-09 11:21:58 LOG: pid 29737: DB node id: 0 backend pid: 29741
> statement: update t1 set i = 10;
> 2010-11-09 11:21:58 LOG: pid 29737: DB node id: 1 backend pid: 29742
> statement: update t1 set i = 10;
> 2010-11-09 11:21:58 ERROR: pid 29737: pgpool detected difference of the
> number of inserted, updated or deleted tuples. Possible last query was:
> "update t1 set i = 10;"
> 2010-11-09 11:21:58 LOG: pid 29737: CommandComplete: Number of affected
> tuples are: 1 0
> 2010-11-09 11:21:58 LOG: pid 29737: ReadyForQuery: Degenerate backends: 1
> 2010-11-09 11:21:58 LOG: pid 29737: ReadyForQuery: Number of affected
> tuples are: 1 0
> 2010-11-09 11:21:58 LOG: pid 29737: notice_backend_error: 1 fail over
> request from pid 29737
> 2010-11-09 11:21:58 LOG: pid 29705: starting degeneration. shutdown host
> (5433)
> 2010-11-09 11:21:58 LOG: pid 29705: failover_handler: set new master node: 0
> 2010-11-09 11:21:58 LOG: pid 29705: failover done. shutdown host (5433)
>
> pgpool.conf attached...
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>> Thanks. I will look into this.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>>> Thank you very much for your answer, the database only has one table with
>>> one column of type integer.
>>> In the first node, the table only has one value (1), in the other node ,
>>> the table is empty, but failover never take place when I execute this
>>> statement "UPDATE tabla1 set id = 2 where id = 1;" .
>>> Can you help me to resolve this problem?
>>> Regards.
>>> Thank you very much for your time.
>>>
>>> ----- "Tatsuo Ishii" <[email protected]> escribi$(D+Q(B:
>>>> > Hello every one.
>>>> > I have this problem.
>>>> > I have one database in two nodes, I use Pgpool-II version 2.3.3.
>>>> > This database has one table, in the first node (master) there is a table
>>>> > with one record and in the other node this table there isn't any record,
>>>> > but when I execute a Select or Update over this table, the secondary
>>>> > node is never degenerate (failover is not perform).
>>>>
>>>> Can you please provide self contained test case? i.e. the SQL and
>>>> table data please.
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese: http://www.sraoss.co.jp
>>>>
>>>> > I tested this problem in Pgpool-II version 3.0.1 and I have the same
>>>> > situation.
>>>> >
>>>> > pgpool.conf of Pgpool-II version 2.3.3.
>>>> > replication_stop_on_mismatch = true
>>>> >
>>>> > pgpool.conf of Pgpool-II version 2.3.3.
>>>> > replication_stop_on_mismatch = true
>>>> > failover_if_affected_tuples_mismatch = true
>>>> >
>>>> > The documentation of pgpool says:
>>>> > failover_if_affected_tuples_mismatch
>>>> >
>>>> > When set to true, if a backend returns number of affected tuples by
>>>> > INSERT/UPDATE/DELETE different between the backends, the backends that
>>>> > differ from most frequent result set are degenerated. If set to false,
>>>> > the session is terminated and the backends are not degenerated. Default
>>>> > is false. replication_stop_on_mismatch
>>>> >
>>>> > When set to true, if a backend returns packet kind different between the
>>>> > backends, the backends that differ from most frequent result set are
>>>> > degenerated. Typical use case is the SELECT statement is part of a
>>>> > transaction and replicate_select is set to true, and SELECT returns
>>>> > diffrent number of rows among backends. Other than SELECT statement
>>>> > might trigger this though. For example, a backend succeeded in an
>>>> > UPDATE, while others failed. Also please note that pgpool does NOT
>>>> > examine content of records returned from SELECT. If set to false, the
>>>> > session is terminated and the backends are not degenerated. Default is
>>>> > false.
>>>> > Anyone knows why is it?
>>>> >
>>>> > Regards.
>>>> > Thank you very much for your time.
>>>> >
>>>>
>> _______________________________________________
>> Pgpool-general mailing list
>> [email protected]
>> http://pgfoundry.org/mailman/listinfo/pgpool-general
Index: pool_proto_modules.c
===================================================================
RCS file: /cvsroot/pgpool/pgpool-II/pool_proto_modules.c,v
retrieving revision 1.88
retrieving revision 1.89
diff -c -r1.88 -r1.89
*** pool_proto_modules.c 19 Oct 2010 08:57:18 -0000 1.88
--- pool_proto_modules.c 20 Oct 2010 00:34:22 -0000 1.89
***************
*** 1,6 ****
/* -*-pgsql-c-*- */
/*
! * $Header: /cvsroot/pgpool/pgpool-II/pool_proto_modules.c,v 1.88 2010/10/19 08:57:18 kitagawa Exp $
*
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
--- 1,6 ----
/* -*-pgsql-c-*- */
/*
! * $Header: /cvsroot/pgpool/pgpool-II/pool_proto_modules.c,v 1.89 2010/10/20 00:34:22 t-ishii Exp $
*
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
***************
*** 1157,1162 ****
--- 1157,1166 ----
degenerate_backend_set(victim_nodes, number_of_nodes);
child_exit(1);
}
+ else
+ {
+ pool_error("ReadyForQuery: find_victim_nodes returned no victim node");
+ }
}
/*
***************
*** 2804,2810 ****
* master_node: The master node id. Less than 0 means ignore this parameter.
* number_of_nodes: Number of elements in victim nodes array.
*
! * Note: If no one wins and master_node > 0, winner would be the
* master and other nodes who has same number of tuples as the master.
*
* Caution: Returned victim node array is allocated in static memory
--- 2808,2814 ----
* master_node: The master node id. Less than 0 means ignore this parameter.
* number_of_nodes: Number of elements in victim nodes array.
*
! * Note: If no one wins and master_node >= 0, winner would be the
* master and other nodes who has same number of tuples as the master.
*
* Caution: Returned victim node array is allocated in static memory
***************
*** 2900,2906 ****
/* Make victim nodes list */
for (i=0;i<nmembers;i++)
{
! if (ntuples[i] > 0 && ntuples[i] != majority_ntuples)
{
victim_nodes[(*number_of_nodes)++] = i;
}
--- 2904,2910 ----
/* Make victim nodes list */
for (i=0;i<nmembers;i++)
{
! if (ntuples[i] >= 0 && ntuples[i] != majority_ntuples)
{
victim_nodes[(*number_of_nodes)++] = i;
}
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
