Thread: Duplicated row after promote in synchronous streaming replication

<div style="font-family: HiraKakuProN-W3; font-size: 13px;">Hi all,</div><div style="font-family: HiraKakuProN-W3;
font-size:13px;"><br /></div><span style="font-family: HiraKakuProN-W3; font-size: 13px;">I'm using PostgreSQL 9.1.10
formy HA project and have found this problem.</span><div style="font-family: HiraKakuProN-W3; font-size: 13px;"><br
/></div><divstyle="font-family: HiraKakuProN-W3; font-size: 13px;">I did (multiple times) the following sequence in my
primary/standbysynchronous replication environment,</div><div style="font-family: HiraKakuProN-W3; font-size:
13px;"><br/></div><div style="font-family: HiraKakuProN-W3; font-size: 13px;">1. Update rows in a table (which have
primarykey constraint column) <span style="line-height: 14.25pt;">in active DB</span></div><div style="font-family:
HiraKakuProN-W3;font-size: 13px;"><br /></div><div style="font-family: HiraKakuProN-W3; font-size: 13px;">2. Stop
activeDB</div><div style="font-family: HiraKakuProN-W3; font-size: 13px;"><br /></div><div style="font-family:
HiraKakuProN-W3;font-size: 13px;">3. Promote standby DB</div><div style="font-family: HiraKakuProN-W3; font-size:
13px;"><br/></div><div style="font-family: HiraKakuProN-W3; font-size: 13px;">4. Confirm the updated table in promoted
standby(new primary) and found that, there's a duplicate updated row (number of row was increased).</div><div
style="font-family:HiraKakuProN-W3; font-size: 13px;"><br /></div><div style="font-family: HiraKakuProN-W3; font-size:
13px;">Ithink it is a replication bug but wonder if it was fixed yet. </div><div style="font-family: HiraKakuProN-W3;
font-size:13px;">Can somebody help me?</div><div style="font-family: HiraKakuProN-W3; font-size: 13px;"><br
/></div><divstyle="font-family: HiraKakuProN-W3; font-size: 13px;">I'm not yet confirm PostgreSQL source, but here
is <spanstyle="font-weight: bold; color: rgb(84, 84, 84); font-family: arial, sans-serif; font-size: small;
line-height:18px; background-color: rgb(255, 255, 255);">my investigation result</span><span style="font-weight: bold;
color:rgb(84, 84, 84); font-family: arial, sans-serif; font-size: small; line-height: 18px; background-color: rgb(255,
255,255);">.</span></div><div style="font-family: HiraKakuProN-W3; font-size: 13px;"><span style="line-height:
14.25pt;"><br/></span></div><div style="font-family: HiraKakuProN-W3; font-size: 13px;"><span style="line-height:
14.25pt;">Updatedtable before promoted were HOT update (index file was not changed).</span></div><div
style="font-family:HiraKakuProN-W3; font-size: 13px;"><br /></div><div style="font-family: HiraKakuProN-W3; font-size:
13px;"><spanstyle="line-height: 14.25pt;">After promote i continue update that duplicated row (it returned two row
updated),and confirm with pg_filedump, </span>i found the duplicated row and only one is related to primary key index
constraint.</div><divstyle="font-family: HiraKakuProN-W3; font-size: 13px;"><br /></div><div style="font-family:
HiraKakuProN-W3;font-size: 13px;">Compare with old active DB, i saw that after promote line pointer of updated row
(duplicatedrow) is broken into two line pointer, the new one is related to primary index constraint and the other is
notrelated to. Some thing like below, </div><div style="font-family: HiraKakuProN-W3; font-size: 13px;"><br
/></div><divstyle="font-family: HiraKakuProN-W3; font-size: 13px;">Old active DB:</div><div style="font-family:
HiraKakuProN-W3;font-size: 13px;">ctid(0,3)->ctid(0,6)->ctid(0,7)</div><div style="font-family: HiraKakuProN-W3;
font-size:13px;"><br /></div><div style="font-family: HiraKakuProN-W3; font-size: 13px;">New active DB (after promote
andupdate):</div><div style="font-family: HiraKakuProN-W3; font-size: 13px;">ctid(0,3)->ctid(0,9)</div><div
style="font-family:HiraKakuProN-W3; font-size: 13px;">ctid(0,7)->ctid(0,10)</div><div style="font-family:
HiraKakuProN-W3;font-size: 13px;"><br /></div><div style="font-family: HiraKakuProN-W3; font-size: 13px;">ctid(0,10) is
notrelated to primary key index constraint.</div><div style="font-family: HiraKakuProN-W3; font-size: 13px;"><br
/></div><divstyle="font-family: HiraKakuProN-W3; font-size: 13px;"><span style="line-height: 14.25pt;">Is something was
wrongin redo log in standby DB? Or line pointer in HOT update feature?</span></div><div style="font-family:
HiraKakuProN-W3;font-size: 13px;"><span style="line-height: 14.25pt;"><br /></span></div><div style="font-family:
HiraKakuProN-W3;font-size: 13px;"><span style="line-height: 14.25pt;">Thanks and best regards,</span></div><div
style="font-family:HiraKakuProN-W3; font-size: 13px;"><span style="line-height: 14.25pt;">Huong,</span></div> 

On 26 March 2014 15:08, Dang Minh Huong <kakalot49@gmail.com> wrote:
> Hi all,
>
> I'm using PostgreSQL 9.1.10 for my HA project and have found this problem.
>
> I did (multiple times) the following sequence in my primary/standby
> synchronous replication environment,
>
> 1. Update rows in a table (which have primary key constraint column) in
> active DB
>
> 2. Stop active DB
>
> 3. Promote standby DB
>
> 4. Confirm the updated table in promoted standby (new primary) and found
> that, there's a duplicate updated row (number of row was increased).
>
> I think it is a replication bug but wonder if it was fixed yet.
> Can somebody help me?
>
> I'm not yet confirm PostgreSQL source, but here is my investigation result.
>
> Updated table before promoted were HOT update (index file was not changed).
>
> After promote i continue update that duplicated row (it returned two row
> updated), and confirm with pg_filedump, i found the duplicated row and only
> one is related to primary key index constraint.
>
> Compare with old active DB, i saw that after promote line pointer of updated
> row (duplicated row) is broken into two line pointer, the new one is related
> to primary index constraint and the other is not related to. Some thing like
> below,
>
> Old active DB:
> ctid(0,3)->ctid(0,6)->ctid(0,7)
>
> New active DB (after promote and update):
> ctid(0,3)->ctid(0,9)
> ctid(0,7)->ctid(0,10)
>
> ctid(0,10) is not related to primary key index constraint.
>
> Is something was wrong in redo log in standby DB? Or line pointer in HOT
> update feature?

It sounds like you're hitting a bug that was introduced in that
exact minor version, and has since been fixed:

http://www.postgresql.org/docs/9.1/static/release-9-1-11.html

You should update to the latest minor version, then re-base your
standbys from the primary.

-- 
Thom

2014/03/27 0:18、Thom Brown <thom@linux.com> のメッセージ:

>> On 26 March 2014 15:08, Dang Minh Huong <kakalot49@gmail.com> wrote:
>> Hi all,
>>
>> I'm using PostgreSQL 9.1.10 for my HA project and have found this problem.
>>
>> I did (multiple times) the following sequence in my primary/standby
>> synchronous replication environment,
>>
>> 1. Update rows in a table (which have primary key constraint column) in
>> active DB
>>
>> 2. Stop active DB
>>
>> 3. Promote standby DB
>>
>> 4. Confirm the updated table in promoted standby (new primary) and found
>> that, there's a duplicate updated row (number of row was increased).
>>
>> I think it is a replication bug but wonder if it was fixed yet.
>> Can somebody help me?
>>
>> I'm not yet confirm PostgreSQL source, but here is my investigation result.
>>
>> Updated table before promoted were HOT update (index file was not changed).
>>
>> After promote i continue update that duplicated row (it returned two row
>> updated), and confirm with pg_filedump, i found the duplicated row and only
>> one is related to primary key index constraint.
>>
>> Compare with old active DB, i saw that after promote line pointer of updated
>> row (duplicated row) is broken into two line pointer, the new one is related
>> to primary index constraint and the other is not related to. Some thing like
>> below,
>>
>> Old active DB:
>> ctid(0,3)->ctid(0,6)->ctid(0,7)
>>
>> New active DB (after promote and update):
>> ctid(0,3)->ctid(0,9)
>> ctid(0,7)->ctid(0,10)
>>
>> ctid(0,10) is not related to primary key index constraint.
>>
>> Is something was wrong in redo log in standby DB? Or line pointer in HOT
>> update feature?
>
> It sounds like you're hitting a bug that was introduced in that
> exact minor version, and has since been fixed:
>
> http://www.postgresql.org/docs/9.1/static/release-9-1-11.html
>

Thanks for your prompt response. I will confirm and revision-up if it is needed.

> You should update to the latest minor version, then re-base your
> standbys from the primary.
>
> --
> Thom