Use exclusion constraints to ensure uniqueness on ElementPath

With the following element structure…

X Y
\ /
 A
/ \
B C

…we could get these paths:

Element	Path	Ordering
A	[X]	0
A	[Y]	0
B	[X, A]	0
B	[Y, A]	0
C	[X, B]	1
C	[Y, B]	1

In theory, an element should only appear once in its parent, but a unique constraint like this would fail if the parent has multiple paths since everything gets duplicated. For the same reason, we cannot prevent an element from having two different orderings in the same parent. We can however use an exclusion constraint to do the equivalent of those constraints:

-- When an element has multiple paths in the same parent, the ordering must be the same (cannot be both "page 2" and "page 3")
ALTER TABLE documents_elementpath
ADD CONSTRAINT unique_element_orderings
EXCLUDE (element_id WITH =, (path[array_length(path, 1)]) WITH =, ordering WITH <>);

-- The orderings in a parent must be unique (cannot have "two page 3s")
ALTER TABLE documents_elementpath
ADD CONSTRAINT unique_parent_orderings
EXCLUDE ((path[array_length(path, 1)]) WITH =, ordering WITH =, element_id WITH <>);

The literal translation of each constraint is:

• For equal values of an element and a parent, its ordering cannot be different.
• For equal values of a parent and an ordering, the element cannot be different.

This still allows duplicate values, as long as they are equal, so we can safely have multiple paths, and our uniqueness constraint will still be ensured.

It might be possible that the btree_gist extension is required for this to function, as well as using EXCLUDE USING GIST (…). I am not exactly sure, because the documentation is unclear on this, but it is only a matter of adding a CREATE EXTENSION in a migration anyway.