Thread: Re: Indexes mysteriously change to ON ONLY
Whoops ... fixed the subject line.
On Fri, Jan 27, 2023 at 3:23 PM Rumpi Gravenstein <rgravens@gmail.com> 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-bitWe 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
Rumpi Gravenstein <rgravens@gmail.com> 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
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 <tgl@sss.pgh.pa.us> wrote:
Rumpi Gravenstein <rgravens@gmail.com> 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
> On Jan 27, 2023, at 13:01, Rumpi Gravenstein <rgravens@gmail.com> 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 ONONLY 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)
Rumpi Gravenstein <rgravens@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
Tom/Christophe I now understand. Thanks for the clear explanation.
On Fri, Jan 27, 2023 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rumpi Gravenstein <rgravens@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
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@sss.pgh.pa.us> wrote:Rumpi Gravenstein <rgravens@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
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.