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

On Sat, Feb 8, 2025 at 12:41 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> hi.
> ```
> drop table if exists idxpart;
> create table idxpart (a int, b int, c text) partition by range (a);
> create table idxpart1 (like idxpart);
> alter table idxpart attach partition idxpart1 for values from (0) to (10);
>
> create index idxpart_c on only idxpart (c) invisible;
> create index idxpart1_c on idxpart1 (c);
>
> alter index idxpart_c attach partition idxpart1_c;
> ```
> In this case, should ALTER INDEX ATTACH PARTITION change the attached
> partition(idxpart1_c)'s "visible" status?
>
>
Hi,
That is a great question and I have really gone back and forth on this one
and here's my reasoning so far

1. When you don't use ONLY:
- The index of child table inherits the visibility of the parent table's
index
- This applies whether the parent index is set as INVISIBLE or VISIBLE
- This automatic inheritance is expected behavior and feels natural

2. When you use ONLY:
- You as a user/developer are explicitly taking control of index
management
- Creating an index for parent as INVISIBLE and another for child as
VISIBLE represents conscious, deliberate choices
- When attaching these indexes, it makes sense to respect these explicit
visibility settings
- Silently overriding the child index's visibility could violate the
Principle of Least Surprise
- Lastly, this model also allows more granular control over index
visibility for each partition

I am not strongly tied to either of these options and very much open to
changing my mind. Also happy to try and document this for more clarity.

I have rebased the patch on top of master (resolving some merge conflicts),
along with the meson changes (thank you for that).

Thanks,
Shayon