Re: Support for REINDEX CONCURRENTLY

On 2012-12-08 09:40:43 -0500, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > On 2012-12-08 21:24:47 +0900, Michael Paquier wrote:
> >> So whatever the method used for swapping: relfilenode switch or relname
> >> switch, you need to modify the pg_class entry of the old and new indexes.
>
> > The point is that with a relname switch the pg_class.oid of the index
> > changes. Which is a bad idea because it will possibly be referred to by
> > pg_depend entries. Relfilenodes - which certainly live in pg_class too,
> > thats not the point - aren't referred to externally though. So if
> > everything else in pg_class/pg_index stays the same a relfilenode switch
> > imo saves you a lot of trouble.
>
> I do not believe that it is safe to modify an index's relfilenode *nor*
> its OID without exclusive lock; both of those are going to be in use to
> identify and access the index in concurrent sessions.  The only things
> we could possibly safely swap in a REINDEX CONCURRENTLY are the index
> relnames, which are not used for identification by the system itself.
> (I think.  It's possible that even this breaks something.)

Well, the patch currently *does* take an exlusive lock in an extra
transaction just for the swapping. In that case it should actually be
safe.
Although that obviously removes part of the usefulness of the feature.

> Even then, any such update of the pg_class rows is dependent on
> switching to MVCC-style catalog access, which frankly is pie in the sky
> at the moment; the last time pgsql-hackers talked seriously about that,
> there seemed to be multiple hard problems besides mere performance.
> If you want to wait for that, it's a safe bet that we won't see this
> feature for a few years.

Yea :(

> I'm tempted to propose that REINDEX CONCURRENTLY simply not try to
> preserve the index name exactly.  Something like adding or removing
> trailing underscores would probably serve to generate a nonconflicting
> name that's not too unsightly.  Or just generate a new name using the
> same rules that CREATE INDEX would when no name is specified.  Yeah,
> it's a hack, but what about the CONCURRENTLY commands isn't a hack?

I have no problem with ending up with a new name or something like
that. If that is what it takes: fine, no problem.

The issue I raised above is just about keeping the pg_depend entries
pointing to something valid... And not changing the indexes pg_class.oid
seems to be the easiest solution for that.

I have some vague schemes in my had that we can solve the swapping issue
with 3 entries for the index in pg_class, but they all only seem to come
to my head while I don't have anything to write them down, so they are
probably bogus.

Greetings,

Andres Freund

--Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services