Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch) - Mailing list pgsql-hackers

From Shayon Mukherjee
Subject Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)
Date
Msg-id BA78DAC7-74C2-4C52-A66E-0D7605412261@gmail.com
Whole thread Raw
In response to Proposal to Enable/Disable Index using ALTER INDEX  (Shayon Mukherjee <shayonj@gmail.com>)
List pgsql-hackers

On Oct 16, 2024, at 2:15 PM, Shayon Mukherjee <shayonj@gmail.com> wrote:


On Oct 16, 2024, at 12:19 PM, Shayon Mukherjee <shayonj@gmail.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

pgsql-hackers by date:

Previous
From: Matthias van de Meent
Date:
Subject: Re: Limiting overshoot in nbtree's parallel SAOP index scans
Next
From: Peter Geoghegan
Date:
Subject: Re: Limiting overshoot in nbtree's parallel SAOP index scans