MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Wed, 24 Jul 2024 01:40:23 +0530
Message-ID: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com>
Subject: Issue while creating index dynamically
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001fd91b061defc1a0"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TVUKY2hxPQMiu78KVXHrZ%2BVL%2BMG_z3Cr8LKqkjs6oCsGg%40mail.gmail.com>
Precedence: bulk

--0000000000001fd91b061defc1a0
Content-Type: text/plain; charset="UTF-8"

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 to
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, something
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 = table_name::regclass
    LOOP
        partition_index_name := 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

Regards
Veem

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

<div dir=3D"ltr">Hi,<div>It&#39;s postgres version 15.4. We have a requirem=
ent to create an index on a big partition table and want to do it online. A=
nd to make the script=C2=A0run in an automated way on any day , through=C2=
=A0our ci/cd pipeline we were trying to write it as below inside a begin/en=
d block. I.e. create index using &quot;ON ONLY&quot; option and then create=
 index on each partition using &#39;CONCURRENTLY&quot; key word and then at=
tach the index partitions to the main index, something as below.=C2=A0<br><=
/div><div><br></div><div>But we are getting an error while 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 wi=
th 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><b=
r>=C2=A0 FOR partition_name IN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT inhrel=
id::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_index_name :=3D parti=
tion_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_n=
ame, partition_name, column_name);<br><br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 EXECU=
TE 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_index_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 transaction 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_index1_idx ON partitio=
ned_table_0 (id);&quot;<br>PL/pgSQL function inline_code_block line 20 at E=
XECUTE</font><br><div><br></div><div>Regards</div><div>Veem</div></div></di=
v>

--0000000000001fd91b061defc1a0--