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

On Mon, Dec 30, 2024 at 3:48 PM Michail Nikolaev <michail(dot)nikolaev(at)gmail(dot)com>
wrote:

> Hello!
>
> One more thing (maybe I missed it in the patch, but anyway) - should we
> add some migration to ensure what old databases will get enabled=true by
> default after upgrade?
>

Hi!

Thank you! I tested this by manually upgrading (using pg_upgrade) from
master to the build from the branch, which ensures that post-upgrade the
column for indisenabled is true by default. I also backed it up with bool
indisenabled BKI_DEFAULT(t); in pg_index.h. Additionally, I tested
upgrading from an old data directory to the new one (both on this patch) to
ensure indexes with DISABLE properties are carried over as well on the new
data directory/upgrade. For reference the latest patch now is in [1].

Given this is working as expected, would we still need a migration step?
(Let me know if I missed something ofc).

For reference here is the setup from my local testing (for reference)

rm -Rf /tmp/pg_data && rm -Rf /tmp/pg_data_new
./configure --prefix=/tmp/pg_install_old && make clean && make -j8 && make
install

# Create and init old cluster
/tmp/pg_install_old/bin/initdb -D /tmp/pg_data
/tmp/pg_install_old/bin/pg_ctl -D /tmp/pg_data start

# Create test data
/tmp/pg_install_old/bin/createdb test
/tmp/pg_install_old/bin/psql test -c "CREATE TABLE foo (id int); CREATE
INDEX idx_foo ON foo(id) DISABLE;"

# Stop old cluster
/tmp/pg_install_old/bin/pg_ctl -D /tmp/pg_data stop

# Switch branch and build new version
git checkout s/enable-disable-index
./configure --prefix=/tmp/pg_install_new && make clean && make -j8 && make
install

# Create new cluster directory
/tmp/pg_install_new/bin/initdb -D /tmp/pg_data_new

# Now run upgrade with different binary locations
/tmp/pg_install_new/bin/pg_upgrade \
-b /tmp/pg_install_old/bin \
-B /tmp/pg_install_new/bin \
-d /tmp/pg_data \
-D /tmp/pg_data_new \
-p 5432 \
-P 5433

/tmp/pg_install_new/bin/pg_ctl -D /tmp/pg_data_new start
$ SELECT * FROM pg_index WHERE indexrelid = 'idx_foo'::regclass;

Thank you
Shayon
[1]
https://www.postgresql.org/message-id/CANqtF-qOtDDktykqSFQO%3DUrDyRuF4fKPBQFaYuY1Eo4M0J8cpA%40mail.gmail.com