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

> On Nov 5, 2024, at 10:55 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Oct 17, 2024 at 9:59 AM Shayon Mukherjee <shayonj@gmail.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
CatalogTupleUpdateto 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
betweenthis 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
wasto avoid it. I did not notice any breakage last time, unless I was looking in the wrong place (possibly?). I did
noticethe min update when a not-in-place update was performed, but I didn't notice any issues (as mentioned in [1])
fromit, via logs, index usage, or other common operations. Let me write up some more test cases to check if there is a
riskof 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