public inbox for [email protected]  

help / color / mirror / Atom feed

From: veem v <[email protected]>
To: pgsql-general <[email protected]>
Subject: Issue while creating index dynamically
Date: Wed, 24 Jul 2024 01:40:23 +0530
Message-ID: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Issue while creating index dynamically
  In-Reply-To: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox