On 01/21/2016 10:42 AM, Frank McGeough wrote:
> I’m having intermittent issues that result in the message "duplicate key
> value violates unique constraint” in the slony logs. Here is my configuration
> :
>
> 1 master
> 2 slaves
> postgresql 9.3.5 on all servers
> slony2.2.2
>
> 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.
>
> 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.
>
> 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.
>
> 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.
Above you say 'the one with the trigger on it that populates the other
table'
You sort of imply you have triggers on tables but don't provide any details.
Do these triggers fire on replicas, the origin, everywhere?
Do the triggers insert data into a replicated table?
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.
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?)
>
> sample error :
>
> 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"
> DETAIL: Key (device_guid, property_guid, data_index)=(26464008, 39, 0)
> already exists.
> 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);"
> COPY sl_log_1, line 36: "1 6117236903 298 7797340846
> device device_properties I 0
> {device_guid,26464008,property_guid,39,data…"
>
> thanks,
> Frank
>
>
> _______________________________________________
> 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
