public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Issue while creating index dynamically
Date: Tue, 23 Jul 2024 16:20:59 -0400
Message-ID: <CANzqJaDZ=1sMh6SH0JWz5_5S=-F+8bMi_1=Cia_gkM9yJTh56w@mail.gmail.com> (raw)
In-Reply-To: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com>
References: <CAB+=1TVUKY2hxPQMiu78KVXHrZ+VL+MG_z3Cr8LKqkjs6oCsGg@mail.gmail.com>

On Tue, Jul 23, 2024 at 4:10 PM veem v <[email protected]> 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.

view thread (2+ messages)  latest in thread

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: <CANzqJaDZ=1sMh6SH0JWz5_5S=-F+8bMi_1=Cia_gkM9yJTh56w@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