On Tue, Jul 23, 2024 at 4:10 PM veem v <veema0000@xxxxxxxxx> 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.
