pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Indexes mysteriously change to ON ONLY
Date: Fri, 27 Jan 2023 20:26:31 -0600
Message-ID: <8a43877f-a282-6d9a-7092-244635b10ad5@gmail.com> (raw)
In-Reply-To: <CAEpg1wA=v6NdeVnNmAuyRsmuUgvnHCCLs3DF4uq8DpviNshC9w@mail.gmail.com>
References: <CAEpg1wBr_apWFXwUOYuU=rz4q=CJ6xmed282CmYM0HQMm3eWFg@mail.gmail.com>
	<CAEpg1wCP2jjGwn+L-JZs3MCJPAU4xzyxBc6-+s2aGEKTuLJChw@mail.gmail.com>
	<3567881.1674852814@sss.pgh.pa.us>
	<CAEpg1wD3gkZ6Px3G4VfWDm+BdOiUbaYJfOm3B=zA+Vr976EjQQ@mail.gmail.com>
	<3570422.1674854184@sss.pgh.pa.us>
	<CAEpg1wA=v6NdeVnNmAuyRsmuUgvnHCCLs3DF4uq8DpviNshC9w@mail.gmail.com>

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.

view thread (7+ messages)

Message-ID: <8a43877f-a282-6d9a-7092-244635b10ad5@gmail.com>
Permalink:  ../8a43877f-a282-6d9a-7092-244635b10ad5@gmail.com/
Also on:    postgresql.org/message-id/8a43877f-a282-6d9a-7092-244635b10ad5@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Indexes mysteriously change to ON ONLY
  In-Reply-To: <8a43877f-a282-6d9a-7092-244635b10ad5@gmail.com>
  References: <CAEpg1wBr_apWFXwUOYuU=rz4q=CJ6xmed282CmYM0HQMm3eWFg@mail.gmail.com> <CAEpg1wCP2jjGwn+L-JZs3MCJPAU4xzyxBc6-+s2aGEKTuLJChw@mail.gmail.com> <3567881.1674852814@sss.pgh.pa.us> <CAEpg1wD3gkZ6Px3G4VfWDm+BdOiUbaYJfOm3B=zA+Vr976EjQQ@mail.gmail.com> <3570422.1674854184@sss.pgh.pa.us> <CAEpg1wA=v6NdeVnNmAuyRsmuUgvnHCCLs3DF4uq8DpviNshC9w@mail.gmail.com> <8a43877f-a282-6d9a-7092-244635b10ad5@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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