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

> On Nov 5, 2024, at 10:55 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Thu, Oct 17, 2024 at 9:59 AM Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
>> My take away from whether or not an in-place update is needed on pg_index [1]
>>
>> - It’s unclear to me why it’s needed.
>> - I understand the xmin would get incremented when using CatalogTupleUpdate to update indisenabled.
>> - However, I haven’t been able to replicate any odd behavior locally or CI.
>> - FWIW - REINDEX CONCURRENTLY (via index_swap), index_constraint_create and few other places perform CatalogTupleUpdate to update the relevant attributes as well.
>>
>> Based on the above summary and after my testing I would like to propose a v3 of the patch. The only difference between this and v1 [2] is that the update of pg_index row happens via CatalogTupleUpdate.
>>
>> [1] https://www.postgresql.org/message-id/20180618215635.m5vrnxdxhxytvmcm@alap3.anarazel.de
>> [2] https://www.postgresql.org/message-id/EF2313B8-A017-4869-9B7F-A24EDD8795DE%40gmail.com
>
> In-place updates are generally bad news, so I think this patch
> shouldn't use them. However, someone will need to investigate whether
> that breaks the indcheckxmin thing that Andres mentions in your [1],
> and if it does, figure out what to do about it. Creating a test case
> to show the breakage would probably be a good first step, to frame the
> discussion.

Hello,

Thank you for the guidance and tips. I was wondering if updating in-place is preferable or not, since my first instinct was to avoid it. I did not notice any breakage last time, unless I was looking in the wrong place (possibly?). I did notice the min update when a not-in-place update was performed, but I didn't notice any issues (as mentioned in [1]) from it, via logs, index usage, or other common operations. Let me write up some more test cases to check if there is a risk of indexcheckxmin running out or other issues, and I'll get back here.

Thank you
Shayon

[1] https://www.postgresql.org/message-id/20180618215635.m5vrnxdxhxytvmcm@alap3.anarazel.de

>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com