postgres index rebuild

2017-05-25 9:38 GMT-03:00 <jashobanta.mahapatra@mahindracomviva.com>:

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

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.

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
<http://www.timbira.com.br&gt;

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

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

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 are the 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 confidential and/or privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and 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 strictly prohibited 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 be implied or assumed unless the sender does so expressly with due authority of Comviva Technologies Limited. E-mail and any contents transmitted with it are prone to viruses and related defects despite all efforts to avoid such by Comviva Technologies Limited.

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs