Horton
Menu

Re: Support for REINDEX CONCURRENTLY

Michael Paquier <michael.paquier@gmail.com>

View thread
From: Michael Paquier <michael.paquier@gmail.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: Andres Freund <andres@2ndquadrant.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Peter Eisentraut <peter_e@gmx.net>, Tom Lane <tgl@sss.pgh.pa.us>, Simon Riggs <simon@2ndquadrant.com>
Date: 2013-03-10T04:46:27Z
Lists: pgsql-hackers 
On Sun, Mar 10, 2013 at 4:50 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

> On Sun, Mar 10, 2013 at 3:48 AM, Fujii Masao <masao.fujii@gmail.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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Background worker processes

  2. Fix assorted bugs in CREATE/DROP INDEX CONCURRENTLY.

  3. Work around unportable behavior of malloc(0) and realloc(NULL, 0).

  4. Properly set relpersistence for fake relcache entries.