Re: Issue while creating index dynamically

On Wed, 24 Jul 2024 at 02:02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ron Johnson <ronljohnsonjr(at)gmail(dot)com> writes:
> > On Tue, Jul 23, 2024 at 4:10 PM veem v <veema0000(at)gmail(dot)com> wrote:
> >> But we are getting an error while executing saying it cant be executed
> in
> >> transaction block with "CONCURRENTLY". So I want to understand , is
> there
> >> any alternate way to get away with this?
>
> > I'd write that in bash, not in a DO block.
>
> Yeah. I thought for a bit about using contrib/dblink to carry out
> the commands in a different session, but I don't think that'll work:
> CREATE INDEX CONCURRENTLY would think it has to wait out the
> transaction running the DO block at some steps. Shove the logic
> over to the client side and you're good to go.
>
> regards, tom lane
>
>
>
Thank you .
I was thinking the individual statement will work fine if I pull out those
from the begin..end block, as those will then be not bounded by any outer
transaction.
However, When I was trying it from dbeaver by extracting individual index
creation statements rather from within the "begin ..end;" block, it still
failed with a different error as below. Why is it so?

"SQL Error [25001]: Error: create index concurrently cannot be executed
within a pipeline "