From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rumpi Gravenstein <rgravens@gmail.com>
Cc: PostgreSQL <pgsql-general@lists.postgresql.org>
Subject: Re: Indexes mysteriously change to ON ONLY
Date: Fri, 27 Jan 2023 16:16:24 -0500
Message-ID: <3570422.1674854184@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEpg1wD3gkZ6Px3G4VfWDm+BdOiUbaYJfOm3B=zA+Vr976EjQQ@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>
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
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: tgl@sss.pgh.pa.us, rgravens@gmail.com
Subject: Re: Indexes mysteriously change to ON ONLY
In-Reply-To: <3570422.1674854184@sss.pgh.pa.us>
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>
* 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