Date: Fri, 5 Apr 2019 12:01:02 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Rahila Syed <rahila.syed@2ndquadrant.com>,
	Andres Freund <andres@anarazel.de>,
	Robert Haas <robertmhaas@gmail.com>,
	Simon Riggs <simon@2ndquadrant.com>,
	Pg Hackers <pgsql-hackers@lists.postgresql.org>,
	David Fetter <david@fetter.org>,
	Pavan Deolasee <pavan.deolasee@gmail.com>,
	Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Subject: Re: monitoring CREATE INDEX [CONCURRENTLY]
Message-ID: <20190405150102.GA2616@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <ef6a6757-c36a-9e81-123f-13b19e36b7d7@2ndquadrant.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On 2019-Apr-05, Peter Eisentraut wrote:

> It seems we can easily extend this to also monitor REINDEX
> [CONCURRENTLY].  Attached is a quick patch.

That's much easier than I was expecting.  I think we should endeavor to
get this done for pg12.

> For the concurrently part, we currently don't have any phases defined
> for the index swap and drop, but maybe we can just skip that initially.
> What happens if we don't have those?

Users are going to wonder why the other phases don't appear to complete
for a long time :-)  Keep in mind that the "waiting" phases are very
confusing to users.  I suggest we just define additional phase numbers
for those phases, then switch the "false" argument to
WaitForLockersMultiple to "true", and it should work :-)  Doc-wise, list
all the phases in the same docbook table, indicate that REINDEX is also
covered, and document in an easier-to-follow fashion which phases each
command goes through.

> It might be nice to have a column in the view not only for the table OID
> but also the index OID.  That is obviously not so useful for CREATE
> INDEX but more useful for REINDEX.  I haven't looked into how adding
> that would work.

Yeah, I think that's simple enough -- the CLUSTER one already does that,
I think.  Another thing for REINDEX TABLE is that we should add a count
of indexes to process, and how many are done.

I was wondering about reporting the command being run.  In the progress_cluster
view we have a "command" column, which says either CLUSTER or VACUUM FULL.
I didn't add one for CREATE INDEX vs. CONCURRENTLY because it seemed to
me that joining to pg_stat_activity ought to be sufficient.  If we agree
with that reasoning, then we should remove the column from the CLUSTER
view, I think.  If not, we should add one to the create_index view.


-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services