public inbox for [email protected]
help / color / mirror / Atom feedRe: Issue while creating index dynamically
2+ messages / 2 participants
[nested] [flat]
* Re: Issue while creating index dynamically
@ 2024-07-23 20:20 Ron Johnson <[email protected]>
2024-07-23 20:32 ` Re: Issue while creating index dynamically Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Ron Johnson @ 2024-07-23 20:20 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Issue while creating index dynamically
2024-07-23 20:20 Re: Issue while creating index dynamically Ron Johnson <[email protected]>
@ 2024-07-23 20:32 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-07-23 20:32 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general <[email protected]>
Ron Johnson <[email protected]> writes:
> On Tue, Jul 23, 2024 at 4:10 PM veem v <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-23 20:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-23 20:20 Re: Issue while creating index dynamically Ron Johnson <[email protected]>
2024-07-23 20:32 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox