Hi folks,
>From the source code, the `ATExecAddIndexConstraint` function does not
support
creating an exclusion constraint from an existing gist index. Here's the
snippet I am talking about:
src/backend/commands/tablecmds.c
```c
/* Note we currently don't support EXCLUSION constraints here */
if (stmt->primary)
constraintType = CONSTRAINT_PRIMARY;
else
constraintType = CONSTRAINT_UNIQUE;
```
I couldn't find the exact reason why in the original commit 88452d5ba6b3e,
which prompted this email as I'm not sure if there is a considerable
limitation today or if the work wasn't viable at the time (2011).
My goal is to perform the following operations:
- Create a gist index CONCURRENTLY.
- From this index, create an exclusion constraint.
When I looked into the feasibility of this, I faced another problem:
Creating the exclusion constraint requires a second pass over the heap,
which
in my local test compounded to 1/3 of the time (see `IndexCheckExclusion`
function for reference). The other 2/3 of the time was spent in the index
creation itself.
I wonder if it's possible to split this operation into two? Creating the
index
first (allowing CONCURRENTLY), and then performing the heap rescan at
another
time? Even if the rescan takes a good chunk of time, it would be preferable
to
at least have the index part not blocking reads/writes. Provided, of
course, we
can guarantee a way to not have conflicts creeping in during the gap between
those operations.
Currently, the only way I found of achieving this is by creating a trigger
that
checks the exclusion manually, with an advisory lock on NEW.id to avoid race
conditions. But this isn't as good as having the constraint itself.
Suggestions about this proposal? Thanks in advance,
Marcelo.
