Обсуждение: [DOCS] postgres index rebuild

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.3/static/routine-reindex.html
Description:

Hi,

As per the doc provided by postgres, for rebuild index we can do
concurrently which doesn't take any lock.
1)create new index concurrently with different name
2)drop old index
3)rename new index with old index name

my question is 

I was trying to keep all the queries(create, drop, alter) in a function. And
'll call the function when required.
But i got below error 
"CREATE INDEX CONCURRENTLY cannot be executed from a function or
multi-command string"

Please help me on this 

Thanks,
jasho

On Fri, May 26, 2017 at 3:24 PM, Euler Taveira <euler@timbira.com.br> wrote:
> CIC can't be executed inside a transaction block or a user-defined function
> because it involves some phases that are not easily rollback-able. Instead
> create a small script with those commands.

If you are interested in the reasons why multiple transactions are
needed, just go through DefineIndex() in indexcmds.c, particularly the
comments. It explains all the phases that are happening for a
concurrent index build, what are the validation phases, what are the
wait phases, etc. The same applies for DROP INDEX with index_drop() in
index.c.
--
Michael

Hi ,

Thank you so much for your quick response.

I have a doubt when you told script.
Are you talking about Shell scripting/ Perl Scripting or any other script.

It 'll be a great pleasure if you can help me with a small example.



-----Original Message-----
From: Michael Paquier [mailto:michael.paquier@gmail.com]
Sent: Saturday, May 27, 2017 1:44 AM
To: Euler Taveira
Cc: Jashobanta Mahapatra; pgsql-docs@postgresql.org
Subject: Re: [DOCS] postgres index rebuild

On Fri, May 26, 2017 at 3:24 PM, Euler Taveira <euler@timbira.com.br> wrote:
> CIC can't be executed inside a transaction block or a user-defined
> function because it involves some phases that are not easily
> rollback-able. Instead create a small script with those commands.

If you are interested in the reasons why multiple transactions are needed, just go through DefineIndex() in
indexcmds.c,particularly the comments. It explains all the phases that are happening for a concurrent index build, what
arethe validation phases, what are the wait phases, etc. The same applies for DROP INDEX with index_drop() in index.c.
 
--
Michael
This e-mail and all material transmitted with it are for the use of the intended recipient(s) ONLY and contains
confidentialand/or privileged information. If you are not the intended recipient, please contact the sender by reply
e-mailand destroy all copies and the original message. Any unauthorized review, use, disclosure, dissemination,
forwarding,printing or copying of this email or any action taken pursuant to the contents of the present e-mail is
strictlyprohibited and is unlawful. The recipient acknowledges that Comviva Technologies Limited or its management or
directors,are unable to exercise control or ensure the integrity over /of the contents of the information contained in
e-mail.Any views expressed herein are those of the individual sender only and no binding nature of the contents shall
beimplied or assumed unless the sender does so expressly with due authority of Comviva Technologies Limited. E-mail and
anycontents transmitted with it are prone to viruses and related defects despite all efforts to avoid such by Comviva
TechnologiesLimited.