Re: Issue while creating index dynamically

On Tue, Jul 23, 2024 at 4:10 PM veem v <veema0000(at)gmail(dot)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 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
>

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