Re: Support for REINDEX CONCURRENTLY

On Sun, Mar 10, 2013 at 4:50 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> On Sun, Mar 10, 2013 at 3:48 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
> wrote:
> > Thanks for updating the patch!
>
> - "SELECT
> reltoastidxid "
> - "FROM info_rels
> i JOIN pg_catalog.pg_class c "
> - " ON
> i.reloid = c.oid"));
> + "SELECT
> indexrelid "
> + "FROM info_rels
> i "
> + " JOIN
> pg_catalog.pg_class c "
> + " ON i.reloid
> = c.oid "
> + " JOIN
> pg_catalog.pg_index p "
> + " ON i.reloid
> = p.indrelid "
> + "WHERE
> p.indexrelid >= %u ", FirstNormalObjectId));
>
> This new SQL doesn't seem to be right. Old one doesn't pick up any indexes
> other than toast index, but new one seems to do.
>
Indeed, it was selecting all indexes...
I replaced it by this query reducing the selection of indexes for toast
relations:
- "SELECT
reltoastidxid "
- "FROM info_rels i
JOIN pg_catalog.pg_class c "
- " ON
i.reloid = c.oid"));
+ "SELECT
indexrelid "
+ "FROM pg_index "
+ "WHERE indrelid
IN (SELECT reltoastrelid "
+ " FROM
pg_class "
+ " WHERE
oid >= %u "
+ " AND
reltoastrelid != %u)",
+
FirstNormalObjectId, InvalidOid));
Will send patch soon...
--
Michael