Thread: [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
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
'll call the function when required.
But i got below error
"CREATE INDEX CONCURRENTLY cannot be executed from a function or
multi-command string"
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
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
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.