MIME-Version: 1.0
References: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com>
In-Reply-To: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 23 Jul 2024 16:20:59 -0400
Message-ID: <CANzqJaDZ=1sMh6SH0JWz5_5S=-F+8bMi_1=Cia_gkM9yJTh56w@mail.gmail.com>
Subject: Re: Issue while creating index dynamically
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000009985f061defe79e"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDZ%3D1sMh6SH0JWz5_5S%3D-F%2B8bMi_1%3DCia_gkM9yJTh56w%40mail.gmail.com>
Precedence: bulk

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

On Tue, Jul 23, 2024 at 4:10=E2=80=AFPM veem v <veema0000@gmail.com> wrote:

> Hi,
> It's postgres version 15.4. We have a requirement to create an index on a
> big partition table and want to do it online. And to make the script run =
in
> an automated way on any day , through our ci/cd pipeline we were trying t=
o
> write it as below inside a begin/end block. I.e. create index using "ON
> ONLY" option and then create index on each partition using 'CONCURRENTLY"
> key word and then attach the index partitions to the main index, somethin=
g
> as below.
>
> 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?
>
>   EXECUTE format('CREATE INDEX %I ON ONLY %I (%I);', index_name,
> table_name, column_name);
>
>   FOR partition_name IN
>         SELECT inhrelid::regclass::text
>         FROM pg_inherits
>         WHERE inhparent =3D table_name::regclass
>     LOOP
>         partition_index_name :=3D partition_name || '_' || index_name ||
> '_idx';
>
>         EXECUTE format('
>             CREATE INDEX CONCURRENTLY %I ON %I (%I);',
> partition_index_name, partition_name, column_name);
>
>         EXECUTE format('
>             ALTER INDEX %I ATTACH PARTITION %I;', index_name,
> partition_index_name);
>     END LOOP;
>
> ********
> ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
> CONTEXT:  SQL statement "
>             CREATE INDEX CONCURRENTLY partitioned_table_0_index1_idx ON
> partitioned_table_0 (id);"
> PL/pgSQL function inline_code_block line 20 at EXECUTE
>

I'd write that in bash, not in a DO block.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Jul 23, 2024 at 4:10=E2=80=AFPM v=
eem v &lt;<a href=3D"mailto:veema0000@gmail.com">veema0000@gmail.com</a>&gt=
; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr">Hi,<div>It&#39;s postgres version 15.4=
. We have a requirement to create an index on a big partition table and wan=
t to do it online. And to make the script=C2=A0run in an automated way on a=
ny day , through=C2=A0our ci/cd pipeline we were trying to write it as belo=
w inside a begin/end block. I.e. create index using &quot;ON ONLY&quot; opt=
ion and then create index on each partition using &#39;CONCURRENTLY&quot; k=
ey word and then attach the index partitions to the main index, something a=
s below.=C2=A0<br></div><div><br></div><div>But we are getting an error whi=
le executing saying it cant be executed=C2=A0in transaction=C2=A0block with=
 &quot;CONCURRENTLY&quot;. So I want to understand , is there any alternate=
 way to get away with this?<br><br><font face=3D"monospace">=C2=A0 EXECUTE =
format(&#39;CREATE INDEX %I ON ONLY %I (%I);&#39;, index_name, table_name, =
column_name);<br><br>=C2=A0 FOR partition_name IN<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 SELECT inhrelid::regclass::text<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM =
pg_inherits<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE inhparent =3D table_name::=
regclass<br>=C2=A0 =C2=A0 LOOP<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 partition_ind=
ex_name :=3D partition_name || &#39;_&#39; || index_name || &#39;_idx&#39;;=
<br><br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 EXECUTE format(&#39;<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 CREATE INDEX CONCURRENTLY %I ON %I (%I);&#39;, =
partition_index_name, partition_name, column_name);<br><br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 EXECUTE format(&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 ALTER INDEX %I ATTACH PARTITION %I;&#39;, index_name, partition_inde=
x_name);<br>=C2=A0 =C2=A0 END LOOP;</font><br>	<br>	********<br><font face=
=3D"monospace">ERROR: =C2=A0CREATE INDEX CONCURRENTLY cannot run inside a t=
ransaction block<br>CONTEXT: =C2=A0SQL statement &quot;<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 CREATE INDEX CONCURRENTLY partitioned_table_0_i=
ndex1_idx ON partitioned_table_0 (id);&quot;<br>PL/pgSQL function inline_co=
de_block line 20 at EXECUTE</font></div></div></blockquote><div><br></div><=
div>I&#39;d write that in bash, not in a DO block.</div><div>=C2=A0</div></=
div></div>

--00000000000009985f061defe79e--