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