<div dir="ltr"><div>Hi,</div><div><br></div><div>I am facing an issue, where one of primary key index got corrupted, due to which duplicate rows got inserted into the table. Same duplicate rows are captured by slony in its sl_log_* and failing to replay those events on slave with duplicate key violation and aborting. Later I found a Slony Faq where it describes the similar situation(I am guessing) under "<b style="color:rgb(0,0,0);font-family:'sans serif';font-size:16px;line-height:16px;background-color:rgb(246,246,246)">8.4. </b><span style="color:rgb(0,0,0);font-family:'sans serif';font-size:16px;line-height:16px;background-color:rgb(246,246,246)">Replication Fails - Unique Constraint Violation" section in this link. </span><a href="http://main.slony.info/documentation/1.2/faq.html#AEN7936">http://main.slony.info/documentation/1.2/faq.html#AEN7936</a></div>
<div><br></div><div>I could able to fix it by removing entries manually from the sl_log_* to continue syncing. </div><div> </div><div>My curious question is, why slony sl_log_* are not capturing distinct values. I believe if it captures the distinct values in sl_log_* then there's no point of sync abort. Correct me if am wrong. </div>
<div>Also, the link describes the situation of Slony 1.2 version whereas am using latest version of slony. Is this still expected in this version of slony ?</div><div><br></div><div>Details of issue for your reference: </div>
<div><b><u>Version:</u></b></div><div><br></div><div>PG 9.3, RHEL, Slony 2.2</div><div><br></div><div><b><u>On Primary</u></b></div><div><br></div><div><div><div>postgres=# \d dtest</div><div> Table "public.dtest"</div>
<div> Column | Type | Modifiers</div><div>--------+---------------+-----------</div><div> id | integer | not null</div><div> name | character(10) |</div><div>Indexes:</div><div> "dtest_pkey" PRIMARY KEY, btree (id)</div>
<div>Triggers:</div><div> _rf_logtrigger AFTER INSERT OR DELETE OR UPDATE ON dtest FOR EACH ROW EXECUTE PROCEDURE _rf.logtrigger('_rf', '2', 'k')</div><div> _rf_truncatetrigger BEFORE TRUNCATE ON dtest FOR EACH STATEMENT EXECUTE PROCEDURE _rf.log_truncate('2')</div>
<div>Disabled triggers:</div><div> _rf_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON dtest FOR EACH ROW EXECUTE PROCEDURE _rf.denyaccess('_rf')</div><div> _rf_truncatedeny BEFORE TRUNCATE ON dtest FOR EACH STATEMENT EXECUTE PROCEDURE _rf.deny_truncate()</div>
</div><div><br></div><div>postgres=# select * from dtest ; /// Due to corruption you have duplicate rows. </div><div> id | name</div><div>----+------------</div><div> 1 | A</div><div> 2 | B</div><div> 3 | C</div>
<div> 4 | D</div><div> 4 | D</div></div><div><br></div><div><div>postgres=# select * from _rf.sl_log_2; /// This has captured the event as it is</div><div> log_origin | log_txid | log_tableid | log_actionseq | log_tablenspname | log_tablerelname | log_cmdtype | log_cmdupdncols | log_cmdargs</div>
<div>------------+----------+-------------+---------------+------------------+------------------+-------------+-----------------+------------------</div><div> 1 | 12292 | 2 | 9 | public | dtest | T | 0 | {}</div>
<div> 1 | 12377 | 2 | 10 | public | dtest | I | 0 | {id,1,name,"A</div><div> 1 | 12389 | 2 | 11 | public | dtest | I | 0 | {id,2,name,"B</div>
<div> 1 | 12400 | 2 | 12 | public | dtest | I | 0 | {id,3,name,"C</div><div> 1 | 13605 | 2 | 13 | public | dtest | I | 0 | {id,4,name,"D</div>
<div> 1 | 13611 | 2 | 14 | public | dtest | I | 0 | {id,4,name,"D</div><div>(6 rows)</div></div><div><br></div><div><br></div><div><b><u>Slony Log information:</u></b></div>
<div><br></div><div><div><font size="1">2014-04-11 11:20:09 PDT ERROR remoteWorkerThread_1_1: error at end of COPY IN: ERROR: duplicate key value violates unique constraint "dtest_pkey"</font></div><div><font size="1">DETAIL: Key (id)=(4) already exists.</font></div>
<div><font size="1">CONTEXT: SQL statement "INSERT INTO "public"."dtest" ("id", "name") VALUES ($1, $2);"</font></div><div><font size="1">COPY sl_log_2, line 1: "1 13611 2 14 public dtest I 0 {id,4,name,"D "}"</font></div>
<div><font size="1">2014-04-11 11:20:09 PDT ERROR remoteWorkerThread_1_1: failed SYNC's log selection query was 'COPY ( select log_origin, log_txid, NULL::integer, log_actionseq, NULL::text, NULL::text, log_cmdtype, NULL::integer, log_cmdargs from "_rf".sl_log_script where log_origin = 1 and log_txid >= "pg_catalog".txid_snapshot_xmax('13607:13607:') and log_txid < '13614' and "pg_catalog".txid_visible_in_snapshot(log_txid, '13614:13614:') union all select log_origin, log_txid, NULL::integer, log_actionseq, NULL::text, NULL::text, log_cmdtype, NULL::integer, log_cmdargs from "_rf".sl_log_script where log_origin = 1 and log_txid in (select * from "pg_catalog".txid_snapshot_xip('13607:13607:') except select * from "pg_catalog".txid_snapshot_xip('13614:13614:') ) union all select log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from "_rf".sl_log_2 where log_origin = 1 and log_tableid in (1) and log_txid >= '13607' and log_txid < '13614' and "pg_catalog".txid_visible_in_snapshot(log_txid, '13614:13614:') union all select log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from "_rf".sl_log_2 where log_origin = 1 and log_tableid in (1) and log_txid in (select * from "pg_catalog".txid_snapshot_xip('13607:13607:') except select * from "pg_catalog".txid_snapshot_xip('13614:13614:') ) union all select log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from "_rf".sl_log_2 where log_origin = 1 and log_tableid in (2) and log_txid >= '13607' and log_txid < '13614' and "pg_catalog".txid_visible_in_snapshot(log_txid, '13614:13614:') union all select log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from "_rf".sl_log_2 where log_origin = 1 and log_tableid in (2) and log_txid in (select * from "pg_catalog".txid_snapshot_xip('13607:13607:') except select * from "pg_catalog".txid_snapshot_xip('13614:13614:') ) union all select log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from "_rf".sl_log_2 where log_origin = 1 and log_tableid in (3) and log_txid >= '13607' and log_txid < '13614' and "pg_catalog".txid_visible_in_snapshot(log_txid, '13614:13614:') union all select log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from "_rf".sl_log_2 where log_origin = 1 and log_tableid in (3) and log_txid in (select * from "pg_catalog".txid_snapshot_xip('13607:13607:') except select * from "pg_catalog".txid_snapshot_xip('13614:13614:') ) order by log_actionseq) TO STDOUT'</font></div>
<div><font size="1">2014-04-11 11:20:09 PDT ERROR remoteWorkerThread_1: SYNC aborted</font></div></div><div><br></div><div>Thank you in advance.</div><div><br></div>-- <br><div>Regards<br></div><div>Raghav</div><div>Blog: htt://<a href="http://raghavt.blogspot.com/" target="_blank">raghavt.blogspot.com/</a></div>
</div>