Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)

On Mon, Nov 25, 2024 at 6:19 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> Another safer option could be to disallow the enable/disable ALTER
> TABLE if indcheckxmin is true. We do have and use
> ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE for these sorts of issues.
> There are other existing failure modes relating to indexes that can
> depend on what another session has done, e.g. MarkInheritDetached()
> can fail if another session detached an index concurrently. I could
> respect an argument that this one might be worse than that as its
> timing dependent rather than dependent on what another session has
> done.
>

Thank you for that feedback David. It's very useful.

I have attached a v4 patch that now raises an error if indcheckxmin is true
before attempting to enable/disable an index, and asks the caller to wait
until indcheckxmin is balanced again via log message.

I couldn't come up with any reliable (non-flaky) way of getting
indcheckxmin to report true in regression specs for extra coverage. So, I
ended up "simulating" it by directly updating the relevant row for an index
and marking indcheckxmin as true in specs and accordingly asserting. The
specs now cover all the previous cases and also the new case where the
error would be raised if the caller attempts to enable/disable an index
while indcheckxmin is true.

The patch is also rebased against the latest master and passing in CI.
Would love to receive any further feedback on it.

Thank you everyone!
Shayon