public inbox for [email protected]
help / color / mirror / Atom feedRe: Issue while creating index dynamically
2+ messages / 2 participants
[nested] [flat]
* Re: Issue while creating index dynamically
@ 2024-07-25 05:42 veem v <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: veem v @ 2024-07-25 05:42 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general <[email protected]>
On Wed, 24 Jul 2024 at 02:02, Tom Lane <[email protected]> wrote:
> Ron Johnson <[email protected]> writes:
> > On Tue, Jul 23, 2024 at 4:10 PM veem v <[email protected]> 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 "
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Issue while creating index dynamically
@ 2024-07-25 07:55 Dominique Devienne <[email protected]>
parent: veem v <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Dominique Devienne @ 2024-07-25 07:55 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: Tom Lane <[email protected]>; Ron Johnson <[email protected]>; pgsql-general <[email protected]>
On Thu, Jul 25, 2024 at 7:42 AM veem v <[email protected]> wrote:
> 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 "
Perhaps it's an artifact of DBeaver using [LibPQ's pipeline mode][1]
when executing scripts?
Maybe give it a try in `psql` instead. Or find a setting to disable
pipeline mode? (which is new to v14).
[1]: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-25 07:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-25 05:42 Re: Issue while creating index dynamically veem v <[email protected]>
2024-07-25 07:55 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox