pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres index rebuild
4+ messages / 4 participants
[nested] [flat]
* postgres index rebuild
@ 2017-05-25 12:38 jashobanta.mahapatra@mahindracomviva.com
0 siblings, 1 reply; 4+ messages in thread
From: jashobanta.mahapatra@mahindracomviva.com @ 2017-05-25 12:38 UTC (permalink / raw)
To: pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres index rebuild
@ 2017-05-26 19:24 Euler Taveira <euler@timbira.com.br>
parent: jashobanta.mahapatra@mahindracomviva.com
0 siblings, 1 reply; 4+ messages in thread
From: Euler Taveira @ 2017-05-26 19:24 UTC (permalink / raw)
To: jashobanta.mahapatra@mahindracomviva.com; +Cc: pgsql-docs
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
<http://www.timbira.com.br;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres index rebuild
@ 2017-05-26 20:14 Michael Paquier <michael.paquier@gmail.com>
parent: Euler Taveira <euler@timbira.com.br>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Paquier @ 2017-05-26 20:14 UTC (permalink / raw)
To: Euler Taveira <euler@timbira.com.br>; +Cc: jashobanta.mahapatra@mahindracomviva.com; pgsql-docs
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: postgres index rebuild
@ 2017-05-29 04:58 Jashobanta Mahapatra <jashobanta.mahapatra@mahindracomviva.com>
parent: Michael Paquier <michael.paquier@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jashobanta Mahapatra @ 2017-05-29 04:58 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; Euler Taveira <euler@timbira.com.br>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-05-29 04:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-25 12:38 postgres index rebuild jashobanta.mahapatra@mahindracomviva.com
2017-05-26 19:24 ` Euler Taveira <euler@timbira.com.br>
2017-05-26 20:14 ` Michael Paquier <michael.paquier@gmail.com>
2017-05-29 04:58 ` Jashobanta Mahapatra <jashobanta.mahapatra@mahindracomviva.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox