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

> On Oct 16, 2024, at 2:15 PM, Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
>
>
>> On Oct 16, 2024, at 12:19 PM, Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
>>
>> - ALTER INDEX ... ENABLE/DISABLE performs an in-place update of the pg_index
>> catalog to protect against indcheckxmin [2] (older unrelated thread).
>
> Performing the in place update of the pg_index row from ATExecEnableDisableIndex using systable_inplace_update_begin was failing in CI weirdly but not on my local MacBook when running spec suite. I am also wondering what is causing the requirement [1] to update the row in-place vs say using CatalogTupleUpdate since using the later is working well locally + CI?
>

I believe I somewhat understand why the issue might be occurring, where CI is failing the create_index regression test and crashing (signal 6 in postmaster logs). I suspect it might be due to a segmentation fault or a similar issue.

IsInplaceUpdateOid is used in systable_inplace_update_begin (recently introduced), but it doesn’t yet support pg_index. Based on check_lock_if_inplace_updateable_rel in heapam.c and IsInplaceUpdateOid in catalog.c, introducing support for in-place updates via this new mechanism might not be straightforward for pg_index. It would require updating the callers to handle in-place updates and locks, I presume?

I did confirm that, based on the v2 PATCH, when not doing in-place updates on pg_index - it means that the xmin for pg_index would increment. I suppose there’s a risk of indcheckxmin being marked as TRUE when xmin exceeds, potentially causing an index to be accidentally skipped ? (I am not 100% sure)

I'll take some time to think this through and familiarize myself with the new systable_inplace_update_begin. In the meantime, aside from the in-place updates on pg_index, I would love to learn any first impressions or feedback on the patch folks may have.

Thank you,
Shayon