Thanks Jan,<div><br></div><div>I'll go ahead and do as recommended. I was actually hoping that maybe we could scour the sl_confirm and/or sl_event tables for clues, but if you think it's not possible to find out what happened, I guess I'll just delete the row and move on.</div>
<div><br></div><div>Thanks again,</div><div>--Richard</div><div><br></div><div><br><br><div class="gmail_quote">On Wed, May 11, 2011 at 1:12 PM, Jan Wieck <span dir="ltr"><<a href="mailto:JanWieck@yahoo.com">JanWieck@yahoo.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div class="im">On 5/11/2011 2:56 PM, Richard Yen wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hello,<br>
<br>
running slon version 2.0.6 here...<br>
</blockquote>
<br></div>
This is the first time I've heard about an operation missing. I remember in some old version of PostgreSQL (8.1 I think) that we experienced duplicate sl_log rows due to index corruption. I was actually able to get a result set with a duplicate ctid. But never was a row missing so far.<br>
<br>
To fix your replica(s), you should be able to manually DELETE the offending row using psql and doing<br>
<br>
set session_replication_role to "replica";<br>
delete from cron_lock where ...<br>
<br>
You need to be a superuser to do so. After that SET statement, the psql prompt will bypass the deny_access triggers and the DELETE statement will behave exactly as if it was coming from slon.<br>
<br>
I assume that by now, a log switch has probably destroyed all traces that could be used to debug the problem further.<br>
<br>
<br>
Jan<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">
<br>
Would anyone be able to help me track down why slony missed a DELETE?<br>
It seems that my replication is broken as the subscribers are trying<br>
to process an INSERT, but a primary key is being violated. The origin<br>
machine does not have the offending tuple, which leads me to believe<br>
that a DELETE was processed, but wasn't propagated to the subscribers<br>
<br>
From my origin machine:<br>
my_db=# select * from cron_lock;<br>
id | lock_until_time<br>
-------------------+----------------------------<br>
anonymous_marking | 2011-05-11 11:40:02.456091<br>
(1 row)<br>
<br>
From my subscriber machines:<br>
my_db=# select * from cron_lock ;<br>
id | lock_until_time<br>
-------------------+----------------------------<br>
anonymous_marking | 2011-05-11 10:40:02.123721<br>
(1 row)<br>
<br>
From the origin's sl_log_* tables:<br>
my_db=# select * from _sac_uk.sl_log_2 where log_tableid =190;<br>
log_origin | log_txid | log_tableid | log_actionseq | log_cmdtype |<br>
log_cmddata<br>
------------+------------+-------------+---------------+-------------+-----------------------------------------------------------------------------------------------------<br>
1 | 1369072247 | 190 | 239698918 | I |<br>
("id","lock_until_time") values ('anonymous_marking','2011-05-11<br>
11:40:02.456091')<br>
1 | 1369182578 | 190 | 239728797 | I |<br>
("id","lock_until_time") values ('<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br></div>
<<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>','2011-05-11 11:00:23.944101')<div class="im"><br>
1 | 1369182587 | 190 | 239728806 | D |<br>
"id"='<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br></div>
<<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>'<div class="im"><br>
1 | 1369182626 | 190 | 239728830 | I |<br>
("id","lock_until_time") values ('<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br></div>
<<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>','2011-05-11<div class="im"><br>
11:00:24.525818') 1 | 1369182671 | 190 | 239728833<br>
| D | "id"='<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br></div>
<<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>'<div class="im"><br>
(5 rows)<br>
<br>
my_db=# select * from _sac_uk.sl_log_1 where log_tableid =190;<br>
log_origin | log_txid | log_tableid | log_actionseq | log_cmdtype |<br>
log_cmddata<br>
------------+------------+-------------+---------------+-------------+------------------------------------------------------------------------------------<br>
1 | 1369393174 | 190 | 239789790 | D |<br>
"id"='anonymous_marking'<br>
1 | 1369403276 | 190 | 239793047 | I |<br>
("id","lock_until_time") values ('anonymous_marking','2011-05-11<br>
12:40:02.970433')(2 rows)<br>
<br>
On the subscriber logs:May 11 11:54:40 uk-sdb2 postgres[30851]: [926-1]<br>
2011-05-11 11:54:40.755 PDT [user=slony,db=my_db 10.1.0.149(47318)<br>
PID:30851 XID:1509911291]ERROR: duplicate key value violates unique<br>
constraint "cron_lock_pkey"<br>
May 11 11:54:40 uk-sdb2 postgres[30851]: [926-2] 2011-05-11 11:54:40.755<br>
PDT [user=slony,db=my_db 10.1.0.149(47318) PID:30851<br>
XID:1509911291]STATEMENT: update only "public"."m_user" set<br>
"last_login"='2011-05-11 10:40:02.429308' where "id"='2459339';<br>
<br>
Any help would be much appreciated.<br>
--Richard<br>
<br>
<br>
<br></div>
_______________________________________________<br>
Slony1-general mailing list<br>
<a href="mailto:Slony1-general@lists.slony.info" target="_blank">Slony1-general@lists.slony.info</a><br>
<a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br>
</blockquote>
<br>
<br>
-- <br>
Anyone who trades liberty for security deserves neither<br>
liberty nor security. -- Benjamin Franklin<br>
</blockquote></div><br></div>