[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Indexes mysteriously change to ON ONLY
From: Ron <ronljohnsonjr () gmail ! com>
Date: 2023-01-28 2:26:31
Message-ID: 8a43877f-a282-6d9a-7092-244635b10ad5 () gmail ! com
[Download RAW message or body]
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.
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
I cheat by using sed to remove "ONLY ON " from the CREATE
statements.<br>
<br>
<div class="moz-cite-prefix">On 1/27/23 15:30, Rumpi Gravenstein
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAEpg1wA=v6NdeVnNmAuyRsmuUgvnHCCLs3DF4uq8DpviNshC9w@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">Tom/Christophe I now understand. Thanks for the
clear explanation.</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Fri, Jan 27, 2023 at 4:16
PM Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us"
moz-do-not-send="true" class="moz-txt-link-freetext">tgl@sss.pgh.pa.us</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Rumpi
Gravenstein <<a href="mailto:rgravens@gmail.com"
target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">rgravens@gmail.com</a>>
writes:<br>
> We are using the pg_indexes view (indexdef) to retrieve
the index<br>
> definition.<br>
<br>
Ah.<br>
<br>
> Are you saying that as a normal part of building an
index, there are short<br>
> periods of time where the pg_indexes view will show the
index with ON ONLY<br>
> specified?<br>
<br>
No, there's no "short periods", this is what it shows. That's
partly<br>
because the output is designed for pg_dump to use. But
there's<br>
a reasonably good argument for it anyway, which is that if you
just<br>
say "create index" then that's effectively a macro for
building the<br>
whole partitioned index set. That pg_indexes entry is only
about the<br>
top-level "virtual" index, and there are other entries for the
leaf<br>
indexes. For example,<br>
<br>
regression=# create table foo (f1 int primary key) partition
by list (f1);<br>
CREATE TABLE<br>
regression=# create table foo_1 partition of foo for values in
(1);<br>
CREATE TABLE<br>
regression=# create table foo_2 partition of foo for values in
(2);<br>
CREATE TABLE<br>
regression=# select tablename,indexname,indexdef from
pg_indexes where indexname like 'foo%';<br>
tablename | indexname |
indexdef <br>
-----------+------------+------------------------------------------------------------------<br>
foo | foo_pkey | CREATE UNIQUE INDEX foo_pkey ON ONLY
public.foo USING btree (f1)<br>
foo_1 | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON
public.foo_1 USING btree (f1)<br>
foo_2 | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON
public.foo_2 USING btree (f1)<br>
(3 rows)<br>
<br>
If you wanted to reconstruct this from individual parts, as
pg_dump does,<br>
you'd issue those commands and then connect them together with
ATTACH<br>
PARTITION commands.<br>
<br>
regards, tom lane<br>
</blockquote>
</div>
<br clear="all">
<div><br>
</div>
-- <br>
<div dir="ltr" class="gmail_signature">Rumpi Gravenstein<input
type="hidden"><input type="hidden"></div>
</blockquote>
<br>
<div class="moz-signature">-- <br>
Born in Arizona, moved to Babylonia.</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic