Re: Indexes mysteriously change to ON ONLY
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 wrote: Rumpi Gravenstein 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.
Re: Indexes mysteriously change to ON ONLY
Tom/Christophe I now understand. Thanks for the clear explanation. On Fri, Jan 27, 2023 at 4:16 PM Tom Lane wrote: > Rumpi Gravenstein 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
Re: Indexes mysteriously change to ON ONLY
Rumpi Gravenstein 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
Re: Indexes mysteriously change to ON ONLY
> On Jan 27, 2023, at 13:01, Rumpi Gravenstein wrote: > > We are using the pg_indexes view (indexdef) to retrieve the index definition. This is as expected. Once the index is created on the partitioned set of tables, the index on the *root* table will be ON ONLY that table; the child tables appear separately: xof=# create table t (i bigint) partition by range(i); CREATE TABLE xof=# create table t001 partition of t for values from (1) to (2); CREATE TABLE xof=# create index on t(i); CREATE INDEX xof=# select * from pg_indexes where tablename = 't'; schemaname | tablename | indexname | tablespace | indexdef +---+---++--- public | t | t_i_idx || CREATE INDEX t_i_idx ON ONLY public.t USING btree (i) (1 row) xof=# select * from pg_indexes where tablename = 't001'; schemaname | tablename | indexname | tablespace | indexdef +---+++ public | t001 | t001_i_idx || CREATE INDEX t001_i_idx ON public.t001 USING btree (i) (1 row)
Re: Indexes mysteriously change to ON ONLY
We are using the pg_indexes view (indexdef) to retrieve the index definition. 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? On Fri, Jan 27, 2023 at 3:53 PM Tom Lane wrote: > Rumpi Gravenstein writes: > >> We have recently discovered that on some of our partitioned tables > indexes > >> that were created as: > >> > >> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) > >> > >> somehow changed to include the ON ONLY option: > >> > >> CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, > >> catalog_id) > > What do you mean "somehow changed"? There is nothing in the system > catalogs that stores that exact string, so I suppose what you mean > is that some tool is presenting the indexes to you that way. > > If that tool is pg_dump, this is its normal behavior. There will > be other commands in its output that build the rest of the > partitioned index set. > > regards, tom lane > -- Rumpi Gravenstein
Re: Indexes mysteriously change to ON ONLY
Rumpi Gravenstein writes: >> We have recently discovered that on some of our partitioned tables indexes >> that were created as: >> >> CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) >> >> somehow changed to include the ON ONLY option: >> >> CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, >> catalog_id) What do you mean "somehow changed"? There is nothing in the system catalogs that stores that exact string, so I suppose what you mean is that some tool is presenting the indexes to you that way. If that tool is pg_dump, this is its normal behavior. There will be other commands in its output that build the rest of the partitioned index set. regards, tom lane
Re: Indexes mysteriously change to ON ONLY
Whoops ... fixed the subject line. On Fri, Jan 27, 2023 at 3:23 PM Rumpi Gravenstein wrote: > We are on PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) > 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bitPostgreSQL 14.5 on > x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat > 8.5.0-10), 64-bit > > We have recently discovered that on some of our partitioned tables indexes > that were created as: > > CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id) > > somehow changed to include the ON ONLY option: > > CREATE UNIQUE INDEX chapter_u01 *ON ONLY *chapter USING btree (dur_uk, > catalog_id) > > There is no SQL issued that explicitly requests this "ON ONLY" option. I > am wondering if this is a side-effect of some other activity. Googling and > looking through documentation haven't helped. > > Does anyone have any thoughts on how this might happen? > > -- > Rumpi Gravenstein > -- Rumpi Gravenstein
