[Pgpool-general] Problem with replication mode!!!

[Tatsuo Ishii]

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" <ishii at sraoss.co.jp> escribió: 
>>>> > 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
>> Pgpool-general at pgfoundry.org
>> http://pgfoundry.org/mailman/listinfo/pgpool-general
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool.patch
Type: text/x-patch
Size: 2321 bytes
Desc: not available
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20101109/3789602b/attachment-0001.bin>