Thanks Jan,

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.

Thanks again,
--Richard



On Wed, May 11, 2011 at 1:12 PM, Jan Wieck <JanWieck at yahoo.com> wrote:

> On 5/11/2011 2:56 PM, Richard Yen wrote:
>
>> Hello,
>>
>> running slon version 2.0.6 here...
>>
>
> 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.
>
> To fix your replica(s), you should be able to manually DELETE the offending
> row using psql and doing
>
>    set session_replication_role to "replica";
>    delete from cron_lock where ...
>
> 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.
>
> I assume that by now, a log switch has probably destroyed all traces that
> could be used to debug the problem further.
>
>
> Jan
>
>
>
>> Would anyone be able to help me track down why slony missed a DELETE?
>>  It seems that my replication is broken as the subscribers are trying
>> to process an INSERT, but a primary key is being violated.  The origin
>> machine does not have the offending tuple, which leads me to believe
>> that a DELETE was processed, but wasn't propagated to the subscribers
>>
>>  From my origin machine:
>> my_db=# select * from cron_lock;
>>         id         |      lock_until_time
>> -------------------+----------------------------
>>  anonymous_marking | 2011-05-11 11:40:02.456091
>> (1 row)
>>
>>  From my subscriber machines:
>> my_db=# select * from cron_lock ;
>>         id         |      lock_until_time
>> -------------------+----------------------------
>>  anonymous_marking | 2011-05-11 10:40:02.123721
>> (1 row)
>>
>>  From the origin's sl_log_* tables:
>> my_db=# select * from _sac_uk.sl_log_2 where log_tableid =190;
>>  log_origin |  log_txid  | log_tableid | log_actionseq | log_cmdtype |
>>                                             log_cmddata
>>
>> ------------+------------+-------------+---------------+-------------+-----------------------------------------------------------------------------------------------------
>>           1 | 1369072247 |         190 |     239698918 | I           |
>> ("id","lock_until_time") values ('anonymous_marking','2011-05-11
>> 11:40:02.456091')
>>           1 | 1369182578 |         190 |     239728797 | I           |
>> ("id","lock_until_time") values ('process_past_due_pm_assignments.pl
>> <http://process_past_due_pm_assignments.pl>','2011-05-11
>> 11:00:23.944101')
>>
>>           1 | 1369182587 |         190 |     239728806 | D           |
>> "id"='process_past_due_pm_assignments.pl
>> <http://process_past_due_pm_assignments.pl>'
>>
>>           1 | 1369182626 |         190 |     239728830 | I           |
>> ("id","lock_until_time") values ('process_past_due_pm_assignments.pl
>> <http://process_past_due_pm_assignments.pl>','2011-05-11
>>
>> 11:00:24.525818')          1 | 1369182671 |         190 |     239728833
>> | D           | "id"='process_past_due_pm_assignments.pl
>> <http://process_past_due_pm_assignments.pl>'
>>
>> (5 rows)
>>
>> my_db=# select * from _sac_uk.sl_log_1 where log_tableid =190;
>>  log_origin |  log_txid  | log_tableid | log_actionseq | log_cmdtype |
>>                                    log_cmddata
>>
>> ------------+------------+-------------+---------------+-------------+------------------------------------------------------------------------------------
>>          1 | 1369393174 |         190 |     239789790 | D           |
>> "id"='anonymous_marking'
>>           1 | 1369403276 |         190 |     239793047 | I           |
>> ("id","lock_until_time") values ('anonymous_marking','2011-05-11
>> 12:40:02.970433')(2 rows)
>>
>> On the subscriber logs:May 11 11:54:40 uk-sdb2 postgres[30851]: [926-1]
>> 2011-05-11 11:54:40.755 PDT [user=slony,db=my_db 10.1.0.149(47318)
>> PID:30851 XID:1509911291]ERROR:  duplicate key value violates unique
>> constraint "cron_lock_pkey"
>> May 11 11:54:40 uk-sdb2 postgres[30851]: [926-2] 2011-05-11 11:54:40.755
>> PDT [user=slony,db=my_db 10.1.0.149(47318) PID:30851
>> XID:1509911291]STATEMENT:  update only "public"."m_user" set
>> "last_login"='2011-05-11 10:40:02.429308' where "id"='2459339';
>>
>> Any help would be much appreciated.
>> --Richard
>>
>>
>>
>> _______________________________________________
>> Slony1-general mailing list
>> Slony1-general at lists.slony.info
>> http://lists.slony.info/mailman/listinfo/slony1-general
>>
>
>
> --
> Anyone who trades liberty for security deserves neither
> liberty nor security. -- Benjamin Franklin
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20110511/b1ab434c/attachment.htm