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

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