pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Indexes mysteriously change to ON ONLY
7+ messages / 4 participants
[nested] [flat]

* Re: Indexes mysteriously change to ON ONLY
@ 2023-01-27 20:24  Rumpi Gravenstein <rgravens@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Rumpi Gravenstein @ 2023-01-27 20:24 UTC (permalink / raw)
  To: PostgreSQL <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Indexes mysteriously change to ON ONLY
@ 2023-01-27 20:53  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Rumpi Gravenstein <rgravens@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2023-01-27 20:53 UTC (permalink / raw)
  To: Rumpi Gravenstein <rgravens@gmail.com>; +Cc: PostgreSQL <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Indexes mysteriously change to ON ONLY
@ 2023-01-27 21:01  Rumpi Gravenstein <rgravens@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Rumpi Gravenstein @ 2023-01-27 21:01 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Indexes mysteriously change to ON ONLY
@ 2023-01-27 21:07  Christophe Pettus <xof@thebuild.com>
  parent: Rumpi Gravenstein <rgravens@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Christophe Pettus @ 2023-01-27 21:07 UTC (permalink / raw)
  To: Rumpi Gravenstein <rgravens@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL <pgsql-general@lists.postgresql.org>



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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Indexes mysteriously change to ON ONLY
@ 2023-01-27 21:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Rumpi Gravenstein <rgravens@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2023-01-27 21:16 UTC (permalink / raw)
  To: Rumpi Gravenstein <rgravens@gmail.com>; +Cc: PostgreSQL <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Indexes mysteriously change to ON ONLY
@ 2023-01-27 21:30  Rumpi Gravenstein <rgravens@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Rumpi Gravenstein @ 2023-01-27 21:30 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Indexes mysteriously change to ON ONLY
@ 2023-01-28 02:26  Ron <ronljohnsonjr@gmail.com>
  parent: Rumpi Gravenstein <rgravens@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Ron @ 2023-01-28 02:26 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-01-28 02:26 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-27 20:24 Re: Indexes mysteriously change to ON ONLY Rumpi Gravenstein <rgravens@gmail.com>
2023-01-27 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-27 21:01   ` Rumpi Gravenstein <rgravens@gmail.com>
2023-01-27 21:07     ` Christophe Pettus <xof@thebuild.com>
2023-01-27 21:16     ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-27 21:30       ` Rumpi Gravenstein <rgravens@gmail.com>
2023-01-28 02:26         ` Ron <ronljohnsonjr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox