Search Postgresql Archives

Re: Indexes mysteriously change to ON ONLY

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I cheat by using sed to remove "ONLY ON " from the CREATE statements.

On 1/27/23 15:30, Rumpi Gravenstein wrote:
Tom/Christophe  I now understand.  Thanks for the clear explanation.

On Fri, Jan 27, 2023 at 4:16 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Rumpi Gravenstein <rgravens@xxxxxxxxx> 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

--
Born in Arizona, moved to Babylonia.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux