On 11-05-11 04:18 PM, Richard Yen wrote: > 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
If you can get a dump of sl_event and sl_confirm of both the master and the slave and send it to one of us we can take a look. If you happen to have slon logs of the period it can't hurt to send those as well. > > > > On Wed, May 11, 2011 at 1:12 PM, Jan Wieck <[email protected] > <mailto:[email protected]>> 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> > <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> > <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> > <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> > <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 > [email protected] > <mailto:[email protected]> > http://lists.slony.info/mailman/listinfo/slony1-general > > > > -- > Anyone who trades liberty for security deserves neither > liberty nor security. -- Benjamin Franklin > > > > > _______________________________________________ > Slony1-general mailing list > [email protected] > http://lists.slony.info/mailman/listinfo/slony1-general _______________________________________________ Slony1-general mailing list [email protected] http://lists.slony.info/mailman/listinfo/slony1-general
