Issue while creating index dynamically

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.

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

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 "

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 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 "

Perhaps it's an artifact of DBeaver using [LibPQ's pipeline mode][1]https://www.postgresql.org/docs/current/libpq-pipeline-mode.html
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