Indexes mysteriously change to LOG

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-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

On 1/27/23 12:23, 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)

How did you do the above without the table name?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

CREATE UNIQUE INDEX chapter_u01 USING btree (dur_uk, catalog_id)

How did you do the above without the table name?

That's a cut/paste error. The original index create is:

create unique index chapter_u01 on chapter (dur_uk,catalog_id);

On Fri, Jan 27, 2023 at 3:34 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 1/27/23 12:23, 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)

How did you do the above without the table name?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

--
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 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)

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.