Re: Indexes vs. cache flushes

On Thu, Jan 19, 2006 at 02:46:11AM -0500, Tom Lane wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >> But you couldn't make any meaningful changes in the definition of an
> >> index, such as changing its column set, operator classes, partial-index
> >> predicate, etc, except by dropping and recreating it.
>
> > The only example that comes to mind of something you might want to be able to
> > twiddle and wouldn't expect to be a slow operation is making a unique index a
> > non-unique index.
>
> I think actually that that would still work, so long as you acquired
> exclusive lock on the parent table first (which you'd have to do anyway,
> because this would constitute a significant change to the table's schema
> --- it could invalidate plans for example). The lock would guarantee
> that no one has the index open. It's only in the case of an opened
> index that I propose not flushing the index support info.
>
> The concerns that I find more interesting are changes in the underlying
> objects. We don't have an ALTER OPERATOR CLASS, much less an ALTER
> ACCESS METHOD, but it's certainly theoretically possible to change the
> definition of a support function used by an index. There isn't
> presently any mechanism to force timely propagation of such a change,
> and so you'd be largely on your own --- but realistically, wouldn't such
> a change require rebuilding the index anyway?

How would this affect changing the type of a column? Specifically, I'm
thinking of the case of domains, where it would be very handy if the
type of a domain could be changed (possibly with some restrictions). IE:
CREATE DOMAIN name varchar(50); is now too small and you want to up it
to varchar(64). Granted, not supported now, though changing constraints
is currently supported.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461