Re: pgsql: REINDEX SCHEMA

On Tue, Dec 9, 2014 at 4:56 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Tue, Dec 9, 2014 at 3:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Simon Riggs <simon@2ndQuadrant.com> writes:
>>> REINDEX SCHEMA
>>
>> The results from jagarundi and leech suggest that more attention needs to
>> be paid to ensuring that tables are reindexed in a consistent order.
>> Either that, or you're going to have to dumb down the regression test.
>
> Hm. The diff is clear:
> ***************
> *** 2852,2859 ****
>   SET SESSION ROLE user_reindex;
>   ERROR:  role "user_reindex" does not exist
>   REINDEX SCHEMA schema_to_reindex;
> - NOTICE:  table "schema_to_reindex.table1" was reindexed
>   NOTICE:  table "schema_to_reindex.table2" was reindexed
>   -- Clean up
>   RESET ROLE;
>   DROP ROLE user_reindex;
> --- 2852,2859 ----
>   SET SESSION ROLE user_reindex;
>   ERROR:  role "user_reindex" does not exist
>   REINDEX SCHEMA schema_to_reindex;
>   NOTICE:  table "schema_to_reindex.table2" was reindexed
> + NOTICE:  table "schema_to_reindex.table1" was reindexed
>   -- Clean up
>   RESET ROLE;
>   DROP ROLE user_reindex;
>
> We could store the results in an array instead of a list and apply a
> qsort to it, but that would be costly if there are many relations
> involved in the reindex. Hence I guess raising client_min_messages to
> warning is fine? I'll send a patch in the REINDEX SCHEMA thread,
> groupped with a couple of other fixes to problems I just found.
Bonus thought: using a plpgsql function that saves relfilenode before
REINDEX SCHEMA and checks that they are updated to different values
after the operation..
--
Michael