<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""> On Jan 21, 2016, at 11:50 AM, Steve Singer <<a href="mailto:steve@ssinger.info" class="">steve@ssinger.info</a>> wrote:<br class=""><div><blockquote type="cite" class=""> <br class="Apple-interchange-newline"><div class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">On 01/21/2016 10:42 AM, Frank McGeough wrote:</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><blockquote type="cite" style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class="">I’m having intermittent issues that result in the message "duplicate key value violates unique constraint” in the slony logs. Here is my configuration :<br class=""><br class="">1 master<br class="">2 slaves<br class="">postgresql 9.3.5 on all servers<br class="">slony2.2.2<br class=""><br class="">All the servers are in the same network and running the same Linux o/s with the same file system. The issues just seem to happen randomly. They happen primarily on one table but have occurred on two different tables up to this point (the tables are logically related in the database - one is populated by events on the other one, the one that has most of the issues is the one with the trigger on it that populates the other table). When the error occurs it may occur on one slave or the other. Once on both.<br class=""><br class="">These incidents have happened 6 times in the past week. Prior to this issue we’ve been running the same setup for many months without incident.<br class=""><br class="">What I do to correct this is to actually remove the entry that is causing the duplicate key issue from the sl_log table. Replication picks back up again. When I compare the rows on all servers I see that they are the same. In all cases when the servers are back in sync the row is actually gone from all servers.<br class=""><br class="">There is no obvious corruption issue on the slave boxes. I’m not sure what else to check at this point. Are there suggestions from the slony developers on how to investigate what is actually going on to cause these issues? Any help is appreciated at this point.<br class=""></blockquote><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Above you say 'the one with the trigger on it that populates the other table'</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">You sort of imply you have triggers on tables but don't provide any details.</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Do these triggers fire on replicas, the origin, everywhere?</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Do the triggers insert data into a replicated table?</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">The conflicting row, you see the second insert for it in sl_log but do you know where the first instance comes from? Is the row recently added? (if so you do you see the first insert in sl_log also) or has the row been there a long time.</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Is the conflicting row otherwise identical to the existing row or is it only identical with respet to the primary key (ie the last_updated times, do they match?)</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""></div></blockquote><div><br class=""></div><div>the triggers only fire on the origin. The two tables in our system are : device_properties and device_graph. device_properties has a slew of triggers :</div><div><br class=""></div><div><div style="margin: 0px; font-size: 11px; font-family: Menlo;" class="">device_properties_change_tg AFTER INSERT OR DELETE OR UPDATE ON device.device_properties FOR EACH ROW EXECUTE PROCEDURE device.device_properties_change()</div><div style="margin: 0px; font-size: 11px; font-family: Menlo;" class="">device_properties_graph_tg AFTER INSERT OR DELETE OR UPDATE ON device.device_properties FOR EACH ROW EXECUTE PROCEDURE device.update_graph()</div><div style="margin: 0px; font-size: 11px; font-family: Menlo;" class="">device_properties_location_id_change_tg AFTER INSERT OR DELETE OR UPDATE ON device.device_properties FOR EACH ROW EXECUTE PROCEDURE device.device_properties_change_location_id()</div><div style="margin: 0px; font-size: 11px; font-family: Menlo;" class="">device_properties_tg AFTER INSERT OR DELETE OR UPDATE ON device.device_properties FOR EACH ROW EXECUTE PROCEDURE device.delete_cached_device_properties()</div><div style="margin: 0px; font-size: 11px; font-family: Menlo;" class="">device_properties_ids_ondelete AFTER DELETE ON device.device_properties FOR EACH ROW EXECUTE PROCEDURE device.process_delete_device_ids()</div><div style="margin: 0px; font-size: 11px; font-family: Menlo;" class="">fax_email_history_tg AFTER DELETE ON device.device_properties FOR EACH ROW EXECUTE PROCEDURE device.fax_email_history()</div><div style="margin: 0px; font-size: 11px; font-family: Menlo;" class=""> t_dirty_device_property_sip_credentials AFTER INSERT OR DELETE OR UPDATE ON device.device_properties FOR EACH ROW EXECUTE PROCEDURE device.fn_t_dirty_device_property_sip_credentials()</div><div class=""><br class=""></div><div class="">all of these triggers are potentially writing data into other tables that are also replicated from origin. </div></div><div><br class=""></div><div>the conflicting row is identical to the existing row. </div><br class=""><blockquote type="cite" class=""><div class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><blockquote type="cite" style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br class="">sample error :<br class=""><br class="">2016-01-21 01:19:01 UTC ERROR remoteWorkerThread_1_1: error at end of COPY IN: ERROR: duplicate key value violates unique constraint "device_properties_pkey"<br class="">DETAIL: Key (device_guid, property_guid, data_index)=(26464008, 39, 0) already exists.<br class="">CONTEXT: SQL statement "INSERT INTO "device"."device_properties" ("device_guid", "property_guid", "data_index", "property_value", "tran_id", "dt_last_updated", "last_updated_by_userid", "version_id") VALUES ($1, $2, $3, $4, $5, $6, $7, $8);"<br class="">COPY sl_log_1, line 36: "1 6117236903 298 7797340846 device device_properties I 0 {device_guid,26464008,property_guid,39,data…"<br class=""><br class="">thanks,<br class="">Frank<br class=""><br class=""><br class="">_______________________________________________<br class="">Slony1-general mailing list<br class=""><a href="mailto:Slony1-general@lists.slony.info" class="">Slony1-general@lists.slony.info</a><br class=""><a href="http://lists.slony.info/mailman/listinfo/slony1-general" class="">http://lists.slony.info/mailman/listinfo/slony1-general</a></blockquote></div></blockquote></div><br class=""></body></html>