Thread: Issue while creating index dynamically

Issue while creating index dynamically

From

veem v

Date:

23 July, 23:10:23

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

Re: Issue while creating index dynamically

From

Ron Johnson

Date:

23 July, 23:20:59

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

Re: Issue while creating index dynamically

From

Tom Lane

Date:

23 July, 23:32:27

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Tue, Jul 23, 2024 at 4:10 PM veem v <veema0000@gmail.com> wrote:
>> 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?

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

Yeah.  I thought for a bit about using contrib/dblink to carry out
the commands in a different session, but I don't think that'll work:
CREATE INDEX CONCURRENTLY would think it has to wait out the
transaction running the DO block at some steps.  Shove the logic
over to the client side and you're good to go.

            regards, tom lane

Re: Issue while creating index dynamically

From

veem v

Date:

25 July, 08:42:15

On Wed, 24 Jul 2024 at 02:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Tue, Jul 23, 2024 at 4:10 PM veem v <veema0000@gmail.com> wrote:
>> 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?

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

Yeah. I thought for a bit about using contrib/dblink to carry out
the commands in a different session, but I don't think that'll work:
CREATE INDEX CONCURRENTLY would think it has to wait out the
transaction running the DO block at some steps. Shove the logic
over to the client side and you're good to go.

regards, tom lane

Thank you .

I was thinking the individual statement will work fine if I pull out those from the begin..end block, as those will then be not bounded by any outer transaction.

However, When I was trying it from dbeaver by extracting individual index creation statements rather from within the "begin ..end;" block, it still failed with a different error as below. Why is it so?

"SQL Error [25001]: Error: create index concurrently cannot be executed within a pipeline "

Re: Issue while creating index dynamically

From

Dominique Devienne

Date:

25 July, 10:55:33

On Thu, Jul 25, 2024 at 7:42 AM veem v <veema0000@gmail.com> wrote:
> I was thinking the individual statement will work fine if I pull out those from the begin..end block, as those will
thenbe not bounded by any outer transaction. 
>  However, When I was trying it from dbeaver by extracting individual index creation statements rather from within the
"begin..end;" block, it still failed with a different error as below. Why is it so? 
>
>  "SQL Error [25001]: Error: create index concurrently cannot be executed within a pipeline "

Perhaps it's an artifact of DBeaver using [LibPQ's pipeline mode][1]
when executing scripts?
Maybe give it a try in `psql` instead. Or find a setting to disable
pipeline mode? (which is new to v14).

[1]: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html