MIME-Version: 1.0
References: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com>
 <CANzqJaDZ=1sMh6SH0JWz5_5S=-F+8bMi_1=Cia_gkM9yJTh56w@mail.gmail.com> <1085896.1721766747@sss.pgh.pa.us>
In-Reply-To: <1085896.1721766747@sss.pgh.pa.us>
From: veem v <veema0000@gmail.com>
Date: Thu, 25 Jul 2024 11:12:15 +0530
Message-ID: <CAB+=1TWipB8gTq2Pop5MWA5e=8oWTAKa3no4UB4n3bbP5E81xg@mail.gmail.com>
Subject: Re: Issue while creating index dynamically
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001a11a7061e0bdc69"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TWipB8gTq2Pop5MWA5e%3D8oWTAKa3no4UB4n3bbP5E81xg%40mail.gmail.com>
Precedence: bulk

--0000000000001a11a7061e0bdc69
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, 24 Jul 2024 at 02:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

--0000000000001a11a7061e0bdc69
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, 24 Jul 2024 at 02:02, Tom Lane &l=
t;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>=
</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com" target=
=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; writes:<br>
&gt; On Tue, Jul 23, 2024 at 4:10=E2=80=AFPM veem v &lt;<a href=3D"mailto:v=
eema0000@gmail.com" target=3D"_blank">veema0000@gmail.com</a>&gt; wrote:<br=
>
&gt;&gt; But we are getting an error while executing saying it cant be exec=
uted in<br>
&gt;&gt; transaction block with &quot;CONCURRENTLY&quot;. So I want to unde=
rstand , is there<br>
&gt;&gt; any alternate way to get away with this?<br>
<br>
&gt; I&#39;d write that in bash, not in a DO block.<br>
<br>
Yeah.=C2=A0 I thought for a bit about using contrib/dblink to carry out<br>
the commands in a different session, but I don&#39;t think that&#39;ll work=
:<br>
CREATE INDEX CONCURRENTLY would think it has to wait out the<br>
transaction running the DO block at some steps.=C2=A0 Shove the logic<br>
over to the client side and you&#39;re good to go.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
<br></blockquote><div><br></div><div>Thank you .</div><div>I was thinking t=
he=C2=A0individual statement will work fine if I pull out those from the be=
gin..end block, as those will then be not bounded by any outer transaction.=
</div><div>=C2=A0However, When I was trying it from dbeaver by extracting=
=C2=A0individual index creation statements rather from within the &quot;beg=
in ..end;&quot; block, it still failed with a different=C2=A0error as below=
. Why is it so?</div><div><br></div><div>=C2=A0&quot;SQL Error=C2=A0[25001]=
: Error: create index concurrently cannot be executed within a pipeline &qu=
ot;=C2=A0</div></div></div>

--0000000000001a11a7061e0bdc69--