Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table pt(
  i int, 
  v float default random())
  partition by range(i);
create table t1 partition of pt for values from(1)to(2);
create table t2 partition of pt for values from(2)to(3);
create table d partition of pt default;
insert into pt values(null),(0),(1),(2),(3),(4);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 6


SELECT (pg_identify_object(addr.classid, addr.objid, addr.objsubid)).*
FROM pg_get_object_address('table', '{pt}','{}') addr;


type
schema
name
identity


table
public
pt
public.pt



SELECT 1


create index concurrently on pt(v);

ERROR:  cannot create index on partitioned table "pt" concurrently


create index concurrently on only pt(v);

ERROR:  cannot create index on partitioned table "pt" concurrently


create extension if not exists dblink;
create index ptpx on only pt(v);

CREATE EXTENSION



CREATE INDEX


begin;

create temp table target_partitions on commit preserve rows
as 
select dblink_connect(concat_ws('_',child.name,'ptpx',row_number()over()),'')
      ,concat_ws('_',child.name,'ptpx',row_number()over()) as new_index_name
      ,child.schema  as child_schema
      ,child.name as child
from pg_inherits as i
cross join lateral pg_get_object_address('table', array[i.inhrelid::regclass::text],'{}')as a
cross join lateral pg_identify_object(a.classid, a.objid, a.objsubid) as child
where i.inhparent = 'pt'::regclass;

--you'd normally dblink_exec(), but db<>fiddle currently experiences issues with it
select dblink_send_query( new_index_name
                   ,format( $q$create index concurrently %3$I on %1$I.%2$I(v);$q$
                           ,child_schema
                           ,child
                           ,new_index_name ))
from target_partitions;

select pg_sleep(3);--db<>fiddle demo only. Normally dblink_exec() query would finish when all its queries complete
commit and chain;--db<>fiddle demo only. By default, you're in autocommit

do $$
declare x text;
begin
for x in select new_index_name from target_partitions loop
  execute format( $q$alter index ptpx attach partition %1$I;$q$
                 ,x);
end loop;
end $$ language plpgsql;

BEGIN



SELECT 3




dblink_send_query


1

1

1



SELECT 3




pg_sleep






SELECT 1



COMMIT



DO


--all indexes created, attached, ready and valid
select indexrelid::regclass
      ,indisready
      ,nmsp_parent.nspname as parent_schema
      ,parent.relname      as parent
      ,nmsp_child.nspname  as child_schema
      ,child.relname       as child
      ,indisvalid
      ,indisready
from pg_inherits
join pg_class as parent          on pg_inherits.inhparent = parent.oid
join pg_class as child           on pg_inherits.inhrelid   = child.oid
join pg_namespace as nmsp_parent on nmsp_parent.oid  = parent.relnamespace
join pg_namespace as nmsp_child  on nmsp_child.oid   = child.relnamespace
join pg_index as i on i.indrelid=child.oid
where parent.relname='pt'
  and nmsp_parent.nspname='public';


indexrelid
indisready
parent_schema
parent
child_schema
child
indisvalid
indisready


t1_ptpx_1
t
public
pt
public
t1
t
t

t2_ptpx_2
t
public
pt
public
t2
t
t

d_ptpx_3
t
public
pt
public
d
t
t



SELECT 3
indexrelid	indisready	parent_schema	parent	child_schema	child	indisvalid	indisready
t1_ptpx_1	t	public	pt	public	t1	t	t
t2_ptpx_2	t	public	pt	public	t2	t	t
d_ptpx_3	t	public	pt	public	d	t	t