From: | Rumpi Gravenstein <rgravens(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Indexes mysteriously change to ON ONLY |
Date: | 2023-01-27 21:30:34 |
Message-ID: | CAEpg1wA=v6NdeVnNmAuyRsmuUgvnHCCLs3DF4uq8DpviNshC9w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom/Christophe I now understand. Thanks for the clear explanation.
On Fri, Jan 27, 2023 at 4:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Rumpi Gravenstein <rgravens(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-01-27 21:31:14 | Re: nextval per counted |
Previous Message | Rob Sargent | 2023-01-27 21:25:32 | Re: nextval per counted |