Horton

ALTER tbl rewrite loses CLUSTER ON index

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Date: 2020-02-02T16:17:18Z
Lists: pgsql-hackers

Attachments

Other options are preserved by ALTER (and CLUSTER ON is and most obviously
should be preserved by CLUSTER's rewrite), so I think (SET) CLUSTER should be
preserved by ALTER, too.

As far as I can see, this should be the responsibility of something in the
vicinity of ATPostAlterTypeParse/RememberIndexForRebuilding.

Attach patch sketches a fix.

ts=# SET client_min_messages=debug; DROP TABLE t; CREATE TABLE t(i int); CREATE INDEX ON t(i)WITH(fillfactor=11, vacuum_cleanup_index_scale_factor=12); CLUSTER t USING t_i_key; ALTER TABLE t ALTER i TYPE bigint; \d t
SET
DEBUG:  drop auto-cascades to type t
DEBUG:  drop auto-cascades to type t[]
DEBUG:  drop auto-cascades to index t_i_idx
DROP TABLE
CREATE TABLE
DEBUG:  building index "t_i_idx" on table "t" serially
CREATE INDEX
ERROR:  index "t_i_key" for table "t" does not exist
DEBUG:  rewriting table "t"
DEBUG:  building index "t_i_idx" on table "t" serially
DEBUG:  drop auto-cascades to type pg_temp_3091172777
DEBUG:  drop auto-cascades to type pg_temp_3091172777[]
ALTER TABLE
                 Table "public.t"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 i      | bigint |           |          | 
Indexes:
    "t_i_idx" btree (i) WITH (fillfactor='11', vacuum_cleanup_index_scale_factor='12')

Commits

  1. Refactor cluster.c to use new routine get_index_isclustered()

  2. Preserve clustered index after rewrites with ALTER TABLE

  3. Preserve replica identity index across ALTER TABLE rewrite

  4. Remove RangeIOData->typiofunc

  5. Preserve pg_index.indisclustered across REINDEX CONCURRENTLY