We run two separate databases which are synchronized through our own replication system (two master databases, so we use our own trigger based methods). Let’s call the two databases “main” and “remote”.
From time to time we get constraint violations on sorting fields classified UNIQUE when users add records belonging the same data entity:
Create table orderpos (
int id not null primary key,
int order not null, -- the entity
int sort not null, -- sorting criteria for reporting purposes
text data,
UNIQUE (order, sort) );
I know that with PostgreSQL 9.5 I can use UPSERT to modify the field “sort” when adding it to the remote database. I simply change the content of the field sort to MAX(sort)+1 for this order. This will resolve the issue on the remote side.
However, the modified value will be replicated back to the main database where the record was generated. My concern is that in the meantime someone might have added another order position to the master database and the value coming back from the remote database is no longer UNIQUE.
Is there a similar concept like UPSERT for UPDATE statements? I.e.
UPDATE orderpos SET sort = 2
WHERE if = 4
ON CONFLICT sort DO UPDATE SET sort = GetSafeSortValue(2)
If not, what is the best way of avoiding a constraint violation in UPDATE statements?
- A BEFORE UPDATE trigger checking the UNIQUE condition and calling GetSafeSort() in case of a conflict?
- A rule?
- Can triggers be used to catch constraint violations?
My least preferred solution would by the replication software handling an exception.
Thanks
Klaus
