Ron Johnson <ronljohnsonjr@gmail.com> writes: > On Tue, Jul 23, 2024 at 4:10 PM veem v <veema0000@gmail.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 "