Horton

Re: 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: Amit Langote <amitlangote09@gmail.com>
Cc: pgsql-hackers@postgresql.org
Date: 2020-02-05T08:32:55Z
Lists: pgsql-hackers 
On Wed, Feb 05, 2020 at 03:53:45PM +0900, Amit Langote wrote:
> Hi Justin,
> 
> On Mon, Feb 3, 2020 at 1:17 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > 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.
> 
> Yes.
> 
> create table foo (a int primary key);
> cluster foo;
> ERROR:  there is no previously clustered index for table "foo"
> cluster foo using foo_pkey;
> alter table foo alter a type bigint;
> cluster foo;
> ERROR:  there is no previously clustered index for table "foo"
> 
> With your patch, this last error doesn't occur.
> 
> Like you, I too suspect that losing indisclustered like this is
> unintentional, so should be fixed.

Thanks for checking.

It doesn't need to be said, but your patch is obviously superior.

I ran into this while looking into a suggestion from Alvaro that ALTER should
rewrite in order of a clustered index (if any) rather than in pre-existing heap
order (more on that another day).  So while this looks like a bug, and I can't
think how a backpatch would break something, my suggestion is that backpatching
a fix is of low value, so it's only worth +0.

Thanks
Justin

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