Tom/Christophe I now understand. Thanks for the clear explanation. On Fri, Jan 27, 2023 at 4:16 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Rumpi Gravenstein <rgrav...@gmail.com> writes: > > We are using the pg_indexes view (indexdef) to retrieve the index > > definition. > > Ah. > > > Are you saying that as a normal part of building an index, there are > short > > periods of time where the pg_indexes view will show the index with ON > ONLY > > specified? > > No, there's no "short periods", this is what it shows. That's partly > because the output is designed for pg_dump to use. But there's > a reasonably good argument for it anyway, which is that if you just > say "create index" then that's effectively a macro for building the > whole partitioned index set. That pg_indexes entry is only about the > top-level "virtual" index, and there are other entries for the leaf > indexes. For example, > > regression=# create table foo (f1 int primary key) partition by list (f1); > CREATE TABLE > regression=# create table foo_1 partition of foo for values in (1); > CREATE TABLE > regression=# create table foo_2 partition of foo for values in (2); > CREATE TABLE > regression=# select tablename,indexname,indexdef from pg_indexes where > indexname like 'foo%'; > tablename | indexname | indexdef > > > -----------+------------+------------------------------------------------------------------ > foo | foo_pkey | CREATE UNIQUE INDEX foo_pkey ON ONLY public.foo > USING btree (f1) > foo_1 | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON public.foo_1 > USING btree (f1) > foo_2 | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON public.foo_2 > USING btree (f1) > (3 rows) > > If you wanted to reconstruct this from individual parts, as pg_dump does, > you'd issue those commands and then connect them together with ATTACH > PARTITION commands. > > regards, tom lane > -- Rumpi Gravenstein
