Proposal to add exclusion constraint from existing index
От | Marcelo Fernandes |
---|---|
Тема | Proposal to add exclusion constraint from existing index |
Дата | |
Msg-id | CAM2F1VO6ymp+GGZQxDxAQvftTiqmux+r+BEpR71fZ7_2gaaPEA@mail.gmail.com обсуждение исходный текст |
Список | pgsql-hackers |
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.
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.
В списке pgsql-hackers по дате отправления: