Re: Re: Allow replacement of bloated primary key indexes without foreign key rebuilds

On Tue, Jul 10, 2012 at 10:44 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Greg Stark <stark(at)mit(dot)edu> writes:
> > On Sat, Jul 7, 2012 at 4:53 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
> wrote:
> >> All we need to do is allow swapping of pg_class.relfilenode of two
> indexes.
>
> > Fwiw I don't like swapping relfilenodes on indexes the user created.
> > REINDEX currently does this but it's a bit of a hack and only works
> > because reindex carefully builds the new index with exactly the same
> > definition as the old one.
>
> Yes. The swap-relfilenodes operation would have to carefully check that
> the index definitions were exactly equivalent, and there would be a
> constant risk for bugs of omission if that code weren't taught about
> any new index properties we invent.
>

IMHO there must be many other places in this code-base where we run that
risk.

The way I am planning to do it was to compare all relevant fields of the
FormData_pg_index. And I am assuming anybody changing the struct members
will take care of relevant changes needed for this code too.

We can add a runtime/compile-time assert to make sure that
Natts_pg_index==17. That way, if a new column gets added, we will get
alerted promptly.

> All of these things seem like ugly, hard-to-use kluges anyway (the
> make-sure-the-indexes-match business is just as much of a PITA for the
> DBA as it is for the system). What we really want is REINDEX
> CONCURRENTLY.
>

+1, but I can't take on that task.

--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company