Re: ALTER TYPE 3: add facility to identify further no-work cases

On Wed, Jan 26, 2011 at 07:52:10PM -0500, Tom Lane wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
> > text -> xml
>
> BTW, that reminds me of something that I think was mentioned way back
> when, but absolutely fails to fit into any of the frameworks discussed
> here: the mere fact that a type is binary-compatible (with or without
> checking) doesn't make it compatible enough to not have to recreate
> indexes. Where and how are we going to have a wart to determine if
> that's necessary?

Design (section 3):
http://archives.postgresql.org/message-id/20101229125625.GA27643@tornado.gateway.2wire.net

Implementation:
http://archives.postgresql.org/message-id/20110113230124.GA18733@tornado.gateway.2wire.net
[disclaimer: I've yet to post an updated version fixing a localized bug in this patch]

I ended up making no attempt to optimize indexes that have predicates or
expression columns; we'll just rebuild them every time. Aside from that, I
failed to find an index on built-in types that requires a rebuild after a type
change optimized by this patch stack. So, the entire wart is really for the
benefit of third-party types that might need it.

> And if the answer is "rebuild indexes whenever the
> data type changes", isn't that a further big dent in the argument that
> it's worth avoiding a table rewrite?

No. Rewriting the table means rebuilding *all* indexes, but the worst case for
a non-rewrite type change is to rebuild all indexes that depend on the changed
column. That's a large win by itself, but we'll usually do even better.

> A text->xml replacement is going
> to be far from cheap anyway.

It's tough to generalize. You can certainly construct a pathological case with
minimal win, but you can just as well construct the opposite. Consider a wide
table with a narrow XML column. Consider a usually-NULL XML column.

nm