How to address duplicate key value violates unique constraint "journal_pkey" when remembering entities in Akka Cluster Sharding?

Akka Akka Cluster
1

For my Akka Typed Persistence service, I’ve turned on remember entities for a couple of my entities. For the both of those actor types, I’m getting a PSQLException: ERROR: duplicate key value violates unique constraint “journal_pkey” with the EventSourcedRememberEntitiesCoordinatorStore actors before the ShardCoordinator shuts down.

How do you recommend resolving this issue properly? How does this issue typically occur? I haven’t seen much guidance online on this issue, so any direction is appreciated.

Thanks!

2

Hi @dmrolfs,

Which plugin are you using? The constraint in the jdbc plugin is journal_pk. That may be just a typo on your side and not relevant here, but I want to check nevertheless.

In any case, I think this is happening because you have two instances of the same actor or two different actors that are sharing the same id. Could that be the case?

3

Hi @octonato,

We are using akka-persistence-jdbc plugin. We directly copied the schema.ddl. We’re using postgres.

Regarding same actor or difference actors using same key: We have two entities that are remembered, and I verified they have separate TypeKey’s during Cluster Shard Entity initialization (from which I believe the EventSourcedRememberEntitiesCoordinatorStore derives its identity).

We do have a deployment situation in which we’re deploying the service twice: once as a canary then the final deployment. Both reference the same database. Can the canary conflict with the final? Aside from the canary deployment, would deploying the service as a single-node cluster impact the behavior?

I’m seeing this duplicate key issue with both:

  • [TypeKey]Shard (on EventSourcedRememberEntitiesShardStore$EntitiesStarted events); and,
 [2021-07-16 07:44:45,452] e[1;31mERRORe[0;39m e[36ma.c.s.i.EventSourcedRememberEntitiesShardStoree[0;39m [] [migration-akka.actor.default-dispatcher-16] - Failed to persist event type [akka.cluster.sharding.internal.EventSourcedRememberEntitiesShardStore$EntitiesStarted] with sequence number [33] for persistenceId [/sharding/MassSuspensionShard/0]. - MDC: {akkaAddress=akka://migration@127.0.0.1:2551, sourceThread=migration-akka.actor.default-dispatcher-12, akkaSource=akka://migration@127.0.0.1:2551/system/sharding/MassSuspension/0/RememberEntitiesStore, sourceActorSystem=migration, akkaTimestamp=07:44:45.452UTC}
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "journal_pkey"
  Detail: Key (persistence_id, sequence_number)=(/sharding/MassSuspensionShard/0, 33) already exists.
  • [TypeKey]Coordinator actors (on String events).
[2021-07-21 02:00:06,980] e[1;31mERRORe[0;39m e[36ma.c.s.i.EventSourcedRememberEntitiesCoordinatorStoree[0;39m [] [migration-akka.actor.default-dispatcher-17] - Failed to persist event type [java.lang.String] with sequence number [7041] for persistenceId [/sharding/MassSuspensionCoordinator]. - MDC: {akkaAddress=akka://migration@127.0.0.1:2551, sourceThread=migration-akka.actor.default-dispatcher-15, akkaSource=akka://migration@127.0.0.1:2551/system/sharding/MassSuspensionCoordinator/singleton/coordinator/RememberEntitiesStore, sourceActorSystem=migration, akkaTimestamp=02:00:06.977UTC}
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "journal_pkey"
  Detail: Key (persistence_id, sequence_number)=(/sharding/MassSuspensionCoordinator, 7041) already exists.

Thanks for your perspective!