Only one unique index - validate my understanding pls

[iamjrock]

I have read about this limitation in the documentation here and I'd like to test my understanding with an example please.

If I'm using multi-active replication and my table structure is as follows....

customer_id (UUID) | customer_name (string) | email_address (string) 

...with these 2 indexes...

Primary Key: [customer_id]
Unique Index: [email_address]

...then this can break replication.

Correct?

If I've understood this correctly, then that would seem to make multi-active replication untenable for many use cases. Are there any workarounds to this? Something like EDB's conflict triggers etc?

[iamjrock]

Any thoughts on this are greatly appreciated please.

[luss]

yes, that could break replication. It is recommended that you make your second index non-unique. The reason is that for conflict resolution there must only be one possible unique key that can be used and can create (potentially) a duplicate key on insert.

…

On Wed, Nov 8, 2023 at 6:11 PM iamjrock ***@***.***> wrote: Any thoughts on this are greatly appreciated please. — Reply to this email directly, view it on GitHub <https://github.com/orgs/pgEdge/discussions/52#discussioncomment-7516183>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHUVXJI6UO6YARLCY63YDQGSNAVCNFSM6AAAAAA6UCD5BWVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TKMJWGE4DG> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[iamjrock]

Ok thx for your insight on this, very much appreciated. We can live without foreign keys etc, but not being able to have unique indexes, in addition to the primary key, is probably too large a sacrifice for our use case.

I imagine that it is an extremely complex, perhaps logically impossible, challenge to solve - but I'll pop the question anyway: Is there any roadmap to being able to safely run multi-master with multiple unique keys in a table?