On Wed, Jan 26, 2011 at 07:52:10PM -0500, Tom Lane wrote: > Noah Misch <n...@leadboat.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.ga27...@tornado.gateway.2wire.net Implementation: http://archives.postgresql.org/message-id/20110113230124.ga18...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers