Re: Support for REINDEX CONCURRENTLY

On Tue, Oct 9, 2012 at 8:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jim Nasby <jim(at)nasby(dot)net> writes:
> > Yeah, what's the risk to renaming an index during concurrent access?
>
> SnapshotNow searches for the pg_class row could get broken by *any*
> transactional update of that row, whether it's for a change of relname
> or some other field.
>
Does it include updates on the relation names of pg_class, or ready and
valid flags in pg_index? Tables refer to the indexes with OIDs only so if
the index and its concurrent are completely separated entries in pg_index,
pg_constraint and pg_class, what is the problem?
Is it that the Relation fetched from system cache might become inconsistent
because of SnapshotNow?

> A lot of these problems would go away if we rejiggered the definition of
> SnapshotNow to be more like MVCC. We have discussed that in the past,
> but IIRC it's not exactly a simple or risk-free change in itself.
> Still, maybe we should start thinking about doing that instead of trying
> to make REINDEX CONCURRENTLY safe given the existing infrastructure.
>
+1. This is something to dig if operations like OID switch are envisaged
for concurrent operations. This does not concern only REINDEX. Things like
CLUSTER, or ALTER TABLE would need something similar.
--
Michael Paquier
http://michael.otacoo.com